﻿using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml.VariantTypes;
using Learun.Application.Base.SystemModule;
using Learun.Util;
using Learun.Util.SqlSugar;
using OfficeOpenXml;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Runtime.CompilerServices;
using System.Text.RegularExpressions;
using System.Windows;
using static Learun.Application.TwoDevelopment.ZZDT_EC.IO_WorkFlowService;
using static Learun.Application.TwoDevelopment.ZZDT_EC.ProjectSugar;
using static Learun.Util.SqlSugar.SqlSugarHelper;

namespace Learun.Application.TwoDevelopment.ZZDT_EC
{
    public enum ImportMode
    {
        /// <summary>
        /// 只能插入
        /// </summary>
        Create = 1,
        /// <summary>
        /// 可以插入和更新（被审核的不会被修改）
        /// </summary>
        CreateUpdate = 2,
        Full = 3
    }



    /// <summary>
    /// 版 本 PIT-ADMS V7.0.3 敏捷开发框架
    /// Copyright (c) 2013-2018 Hexagon PPM
    /// 创 建：超级管理员
    /// 日 期：2022-06-21 15:32
    /// 描 述：业务层
    /// </summary>
    public class ec_Wire_GroupBLL : ec_Wire_GroupIBLL
    {
        IO_WorkFlowService ec_Wire_GroupService;
        ec_Wire_GroupService service;
        const string strMessageForConfirmAndDel = "信号已被审核或者已删除";
        /// <summary>
        /// 用这个ctor的时候，IO_WorkFlowService 用不了
        /// </summary>
        public ec_Wire_GroupBLL()
        {
            service = new ec_Wire_GroupService();

        }
        /// <summary>
        /// 用到这个的api接口，必须要有  [HandlerApiLogin(FilterMode.Enforce)]
        /// </summary>
        /// <param name="ProjId"></param>
        public ec_Wire_GroupBLL(string ProjId)
        {
            var user = LoginUserInfo.Get();
            ec_Wire_GroupService = new IO_WorkFlowService(ProjId, user.userId);
        }
        #region 获取数据
        /// <summary>
        /// 信号，包括了虚拟点集合
        /// </summary>
        /// <param name="queryJson"></param>
        /// <param name="Assigned">true，只返回有通道关联的</param>
        /// <param name="tree">true，虚拟点放主信号下面。false，平行查询</param>
        /// <param name="IncludeDelete">true，所有的。false，仅仅是未删除的</param>
        /// <returns></returns>
        public List<ec_Wire_GroupEntity> GetList(string queryJson, bool Assigned = true, bool tree = false, bool IncludeDelete = true, Pagination pagination = null, bool onlySelf = false)
        {
            var queryParam = queryJson.ToJObject();
            var ProjectId = queryParam["ProjectId"]?.ToString();
            var WireGroupTemplateID = queryParam["WireGroupTemplateID"]?.ToString();
            var Group_Name = queryParam["Group_Name"]?.ToString();
            var Search = queryParam["Search"]?.ToString(); //全局搜索
            var ChannelID = queryParam["ChannelID"]?.ToString();
            var ChannelIDs = queryParam["ChannelIDs"]?.ToString();
            //var StripID = queryParam["StripID"]?.ToString();
            var Status = 0;
            int.TryParse(queryParam["Status"]?.ToString(), out Status);

            var tableName = TableName<ec_Wire_GroupEntity>(ProjectId);
            var panelStripTermTableName = TableName<ec_PanelStripTermEntity>(ProjectId);
            var query = Db.Queryable<ec_Wire_GroupEntity>().AS(tableName)
                .WhereIF(!string.IsNullOrEmpty(WireGroupTemplateID), x => x.WireGroupTemplateID == WireGroupTemplateID)
                .WhereIF(!string.IsNullOrEmpty(Group_Name), x => x.Group_Name.Contains(Group_Name))
                .WhereIF(!string.IsNullOrEmpty(ChannelID), x => x.ChannelID == ChannelID)
                .WhereIF(!string.IsNullOrEmpty(ChannelIDs), x => SqlFunc.SplitIn(ChannelIDs, x.ChannelID))
                .WhereIF(!string.IsNullOrEmpty(Search),
                    x => SqlFunc.FullTextContains(new string[]{ x.Group_Name, x.Group_Desc_EN, x.Group_Desc, x.Code,
                            x.Unit, x.Supplier, x.SENSOR_CODE, x.Remarks }, Search))
                .WhereIF(Status > 0, x => x.Status == (WireGroupStatusEnum)Status)
                .WhereIF(!IncludeDelete, x => x.DeleteFlg == false)
                .WhereIF(Assigned, x => !string.IsNullOrEmpty(x.ChannelID))
                //.WhereIF(!string.IsNullOrEmpty(StripID), x => x.ChannelID == SqlFunc.Subqueryable<ec_PanelStripTermEntity>().AS(panelStripTermTableName)
                //.Where(y => y.StripID == StripID).Select(y => y.ChannelID))
                .OrderBy(x => x.Group_Name);
            List<ec_Wire_GroupEntity> list;
            if (pagination == null)
            {
                list = query.ToList();
            }
            else
            {
                int totalNumber = 0;
                int totalPage = 0;
                list = query.ToPageList(pagination.page, pagination.rows, ref totalNumber, ref totalPage);
                pagination.records = totalNumber;
                pagination.total = totalPage;
            }
            if (onlySelf)
            {
                return list;
            }
            #region 对状态做一些修正
            foreach (ec_Wire_GroupEntity signal in list.Where(x => x.Status == WireGroupStatusEnum.New && !string.IsNullOrEmpty(x.ChannelID)))
            {
                //明明有通道关联了，但是状态依旧是02的
                signal.Status = WireGroupStatusEnum.Used;
                Db.Updateable(signal).AS(tableName).
                IgnoreColumns(x => x.CreateTime).
                IgnoreColumns(x => x.CreateUserID).ExecuteCommand();
            }
            foreach (ec_Wire_GroupEntity signal in list.Where(x => x.Status == WireGroupStatusEnum.Used && string.IsNullOrEmpty(x.ChannelID) && string.IsNullOrEmpty(x.CableId))) //针对有cableid,即通讯点关联了母线的，不用管，因为就是没有channelid
            {
                //明明没通道关联了，但是状态依旧是03的
                signal.Status = WireGroupStatusEnum.New;
                Db.Updateable(signal).AS(tableName).
                IgnoreColumns(x => x.CreateTime).
                IgnoreColumns(x => x.CreateUserID).ExecuteCommand();
            }
            #endregion
            #region 如果单纯用自动化平台，不做关联
            var settingTableName = TableName<ec_projectSettingsEntity>(ProjectId);
            var setObj = Db.Queryable<ec_projectSettingsEntity>().AS(settingTableName).First(x => x.SettingName == "IO_AllowManualAssign");
            if (setObj != null && setObj.SettingValue.ToLower() == "true")
            {
                return list;
            }
            else
            {
                //关联
                var panelTableName = TableName<ec_PanelEntity>(ProjectId);
                var enginedataTableName = TableName<ec_enginedataEntity>(ProjectId);
                var channelTableName = TableName<ec_PanelChannelEntity>(ProjectId);
                var stripTableName = TableName<ec_PanelStripEntity>(ProjectId);
                var wireTerminalTableName = TableName<ec_WireTerminalEntity>(ProjectId);
                var cableSetTableName = TableName<ec_CableSetEntity>(ProjectId);
                var wireTableName = TableName<ec_CableSetWireEntity>(ProjectId);
                var cableTableName = TableName<ec_CableEntity>(ProjectId);

                var panels = Db.Queryable<ec_enginedataEntity>().AS(enginedataTableName)
                    .InnerJoin<ec_PanelEntity>((a, b) => a.EngineDataID == b.EngineerDataID).AS<ec_PanelEntity>(panelTableName)
                    .Select((a, b) => new { b.PanelID, a.TagNumber }).ToList();
                var channelIDs = list.Where(x => !string.IsNullOrEmpty(x.ChannelID)).Select(x => x.ChannelID).Distinct().ToList();
                var channels = Db.Queryable<ec_PanelChannelEntity>().AS(channelTableName)
                    .Where(a => channelIDs.Contains(a.ChannelID))
                    .Select(a => new { a.ChannelID, a.ChannelName, a.StripID }).ToList();
                var stripIDs = channels.Select(x => x.StripID).Distinct().ToList();
                var strips = Db.Queryable<ec_PanelStripEntity>().AS(stripTableName)
                    .Where(a => stripIDs.Contains(a.StripID))
                    .Select(a => new { a.StripID, a.StripName, a.PanelID })
                    .ToList();
                var homerunCables = Db.Queryable<ec_CableEntity>().AS(cableTableName)
                     .InnerJoin<ec_enginedataEntity>((a, b) => a.EngineerDataID == b.EngineDataID).AS<ec_enginedataEntity>(enginedataTableName)
                     .LeftJoin<ec_PanelChannelEntity>((a, b, c) => a.CableID == c.HomerunCableId).AS<ec_PanelChannelEntity>(channelTableName)
                     .LeftJoin<ec_PanelStripEntity>((a, b, c, d) => c.StripID == d.StripID).AS<ec_PanelStripEntity>(stripTableName)
                     .Where(a => list.Select(x => x.CableId).Distinct().Contains(a.CableID))
                     .Select((a, b, c, d) => new { a.CableID, cableName = b.TagNumber, c.ChannelName, d.StripName, d.PanelID })
                     .ToList();
                var cables = Db.Queryable<ec_PanelStripTermEntity>().AS(panelStripTermTableName)
                     .Where(a => channelIDs.Contains(a.ChannelID))
                     .InnerJoin<ec_WireTerminalEntity>((a, b) => a.TermID == b.TermID).AS<ec_WireTerminalEntity>(wireTerminalTableName)
                     .InnerJoin<ec_CableSetWireEntity>((a, b, c) => b.WireID == c.WireID).AS<ec_CableSetWireEntity>(wireTableName)
                     .InnerJoin<ec_CableSetEntity>((a, b, c, d) => c.CableSetID == d.CableSetID).AS<ec_CableSetEntity>(cableSetTableName)
                     .InnerJoin<ec_CableEntity>((a, b, c, d, e) => d.CableID == e.CableID).AS<ec_CableEntity>(cableTableName)
                     .InnerJoin<ec_enginedataEntity>((a, b, c, d, e, f) => e.EngineerDataID == f.EngineDataID).AS<ec_enginedataEntity>(enginedataTableName)
                     .Distinct()
                     .Select((a, b, c, d, e, f) => new { a.ChannelID, d.CableSetName, f.TagNumber })
                     .ToList();
                #region 填充panel和电缆信息
                foreach (var signal in list.Where(x => x.Status != WireGroupStatusEnum.ToDelete))
                {
                    if (signal.CommunicationPoint)
                    {
                        //b
                        var homerun = homerunCables.FirstOrDefault(x => x.CableID == signal.CableId);
                        if (homerun != null && !string.IsNullOrEmpty(homerun.ChannelName))
                        {
                            signal.CableSetName = "";
                            signal.CableName = "(母线)" + homerun.cableName;
                            signal.ChannelName = homerun.ChannelName;
                            signal.StripName = homerun.StripName;
                            signal.PanelName = panels.FirstOrDefault(x => x.PanelID == homerun.PanelID)?.TagNumber;
                            if (signal.Status == WireGroupStatusEnum.New && !string.IsNullOrEmpty(homerun.ChannelName))
                            {
                                signal.Status = WireGroupStatusEnum.Used;
                            }

                        }
                        else
                        {
                            if (signal.Status == WireGroupStatusEnum.Used)
                            {
                                signal.Status = WireGroupStatusEnum.New;
                            }

                        }

                    }
                    else if (!string.IsNullOrEmpty(signal.ChannelID))
                    {
                        var ch = channels.FirstOrDefault(x => x.ChannelID == signal.ChannelID);
                        var strip = strips.FirstOrDefault(x => x.StripID == ch.StripID);
                        var cable = cables.FirstOrDefault(x => x.ChannelID == signal.ChannelID);
                        signal.CableSetName = cable?.CableSetName;
                        signal.CableName = cable?.TagNumber;
                        signal.ChannelName = ch?.ChannelName;
                        signal.StripName = strips.FirstOrDefault(x => x.StripID == ch.StripID)?.StripName;
                        signal.PanelName = panels.FirstOrDefault(x => x.PanelID == strip.PanelID)?.TagNumber;
                    }
                    switch (signal.Status)
                    {
                        case WireGroupStatusEnum.ToDelete:
                            signal.StatusValue = "待删除";
                            break;
                        case WireGroupStatusEnum.New:
                            signal.StatusValue = "新增";
                            break;
                        case WireGroupStatusEnum.Used:
                            signal.StatusValue = "已关联";
                            break;
                        case WireGroupStatusEnum.Confirmed:
                            signal.StatusValue = "已审核";
                            break;
                        case WireGroupStatusEnum.Reopen:
                            signal.StatusValue = "重现打开";
                            break;
                        default:
                            break;
                    }
                }
                #endregion
            }
            #endregion
            if (tree)
            {
                var signalsHasSoftTag = list.Where(x => string.IsNullOrEmpty(x.ParentID)).ToList();
                foreach (var signal in signalsHasSoftTag)
                {
                    signal.NewSoftTags.AddRange(list.Where(x => x.ParentID == signal.Wire_Group_ID));
                }
                list = signalsHasSoftTag;
            }
            return list;
        }
        /// <summary>
        /// 获取左侧树形数据
        /// <summary>
        /// <param name="ProjectId">项目ID</param>
        /// <returns></returns>
        public List<TreeModel> GetTree(string ProjectId)
        {
            try
            {
                var list = service.GetTemplateList(ProjectId);
                List<TreeModel> treeList = new List<TreeModel>();
                list.ForEach(x =>
                {
                    TreeModel node = new TreeModel
                    {
                        id = x.WireGroupTemplateID.ToString(),
                        text = x.TemplateName.ToString(),
                        value = x.WireGroupTemplateID.ToString(),
                        showcheck = false,
                        checkstate = 0,
                        isexpand = true,
                        parentId = ""
                    };
                    treeList.Add(node);
                });
                return treeList.ToTree();
            }
            catch (Exception ex)
            {
                if (ex is ExceptionEx)
                {
                    throw;
                }
                else
                {
                    throw ExceptionEx.ThrowBusinessException(ex);
                }
            }
        }
        /// <summary>
        /// 获取实体数据
        /// <param name="keyValue">主键</param>
        /// <summary>
        /// <returns></returns>
        public ec_Wire_GroupEntity GetEntity(string keyValue, string ProjectId)
        {
            try
            {
                return service.GetEntity(ProjectId, keyValue);
            }
            catch (Exception ex)
            {
                if (ex is ExceptionEx)
                {
                    throw;
                }
                else
                {
                    throw ExceptionEx.ThrowBusinessException(ex);
                }
            }
        }

        #endregion

        #region 提交数据
        public string DeleteEntity(string ProjectId, string ID, bool OnlyMessage)
        {
            var user = LoginUserInfo.Get();
            var service = new IO_WorkFlowService(ProjectId, user.userId);
            var res = service.Delete(ProjectId, ID, OnlyMessage);
            //if (string.IsNullOrEmpty(res))
            //{
            //    ec_Wire_GroupService.DeleteEntity(ProjectId, ID);
            //}
            return res;
        }
        /// <summary>
        /// 修改 或 新建信号
        /// </summary>
        /// <param name="ProjectId"></param>
        /// <param name="ID"></param>
        /// <param name="entity"></param>
        /// <exception cref="Exception"></exception>
        public string SaveEntity(string ProjectId, ref string ID, ec_Wire_GroupEntity entity, string MODE = "0")
        {
            try
            {
                var existSignals = this.GetList("{ProjectId:\"" + ProjectId + "\"}", false);
                string wireGroupId = ID;
                var existObj = existSignals.FirstOrDefault(x => x.Wire_Group_ID == wireGroupId);
                #region 过滤掉一些无效的 
                if (existObj?.DeleteFlg == true)
                {
                    if (!entity.DeleteFlg == false)
                    {
                        return "";//完全删除的东西 暂时就不让修改了
                    }
                    else
                    {
                        //说明刻意取消的
                    }
                }
                if (entity.CommunicationPoint == false
                    && string.IsNullOrEmpty(entity.Signal_Group)
                    && string.IsNullOrEmpty(entity.ParentID))
                {
                    return "";//通讯点
                }
                if (string.IsNullOrEmpty(entity.Group_Name))
                {
                    return "";
                }
                #endregion

                //ProjectId = ec_Wire_GroupService.ProjIndex;
                string res = "";
                //这里要判断下，因为一个通道下只有一个信号
                //所以如果保存一个新的信号时，要注意把原来的占据这个通道的那个信号的channelID信息给清空掉。
                var LogTableName = ProjectSugar.TableName<ec_wire_group_logEntity>(ProjectId);
                var settingsTableName = ProjectSugar.TableName<ec_projectSettingsEntity>(ProjectId);
                var setObj = SqlSugarHelper.Db.Queryable<ec_projectSettingsEntity>().AS(settingsTableName).
                    First(x => x.SettingName == "IO_AllowManualAssign");

                #region 修正一些必要的列值
                if (!string.IsNullOrEmpty(entity.ParentID) && string.IsNullOrEmpty(entity.Group_Name))
                {
                    var masterSignal = existSignals.FirstOrDefault(x => x.Wire_Group_ID == entity.ParentID);
                    if (masterSignal != null)
                    {
                        entity.Group_Name = masterSignal.Group_Name + "_" + entity.Code;
                    }
                }
                entity.Signal_Group = entity.Signal_Group.Replace("_", "");
                if (setObj != null && setObj.SettingValue.ToLower() == "true")
                {
                    //手动关联模式
                    if (string.IsNullOrEmpty(entity.CableName.Replace(" ", "")))
                    {
                        entity.ChannelID = ""; //如果是空，就认为没有关联了，毕竟此时电缆都没了
                        entity.Status = WireGroupStatusEnum.New;
                    }
                    else
                    {
                        entity.ChannelID = "0";//如果不为空，就认为关联了，因为此时暂不会真正有channelID
                        entity.Status = WireGroupStatusEnum.Used;
                    }
                }
                else
                {
                    entity.CableName = ""; //
                }
                if (!entity.CommunicationPoint)
                {
                    entity.CableId = "";  //只有通讯点，才有意义
                }
                else
                {
                    entity.CableId = existObj?.CableId;
                }
                #endregion



                if (entity.ActionHistorys == null)//关联或者修改时，会没有
                {
                    if (string.IsNullOrEmpty(ID))
                    {
                        res = ec_Wire_GroupService.Add(entity, ref ID);//第一次运行new之后,ID就有值了
                        if (!string.IsNullOrEmpty(res)) return res;
                    }
                    else
                    {

                        if (existObj == null)
                        {
                            res = "数据库中查无此信号。";
                            return res;
                        }
                        else if (existObj.Status != WireGroupStatusEnum.ToDelete && entity.Status == WireGroupStatusEnum.ToDelete)
                        {
                            res = ec_Wire_GroupService.ReadyDelete(entity, existObj, "  ", true, MODE);
                        }
                        else if ((existObj.Status == WireGroupStatusEnum.New || existObj.Status == WireGroupStatusEnum.Used || existObj.Status == WireGroupStatusEnum.Reopen) &&
                            entity.Status == WireGroupStatusEnum.Confirmed)
                        {
                            res = ec_Wire_GroupService.Approve(entity, true, MODE);
                        }
                        else if (existObj.Status == WireGroupStatusEnum.Confirmed && entity.Status == WireGroupStatusEnum.Reopen)
                        {
                            ec_Wire_GroupService.Reopen(entity, existObj, "  ", true, MODE);
                        }
                        else if (existObj.Status == WireGroupStatusEnum.ToDelete && entity.Status != WireGroupStatusEnum.ToDelete)
                        {
                            ec_Wire_GroupService.UnDoDelete(entity, true, MODE);
                        }
                        else if (existObj.Status == WireGroupStatusEnum.Confirmed && entity.Status == WireGroupStatusEnum.Confirmed)
                        {
                            //无法修改
                            //因此导致客户那边通道迁移出现问题。
                            //客户需要审核后允许通道迁移
                            if (entity.ChannelID != existObj.ChannelID)
                            {
                                res = ec_Wire_GroupService.MoveToAnotherCh(entity, existObj);
                            }

                        }
                        else
                        {
                            res = ec_Wire_GroupService.Modify(entity, existObj, MODE);
                        }

                        if (!string.IsNullOrEmpty(res))
                        {
                            return res;//出错
                        }
                        if (existObj.ElecOnly != entity.ElecOnly)
                        {
                            var noticeObj = new ec_wire_group_noticeEntity(Action.修改,
                                ec_Wire_GroupService._Department == Department.电气 ? Department.轮机 : Department.电气, entity.Wire_Group_ID);
                            noticeObj.Create();
                            noticeObj.Message = $"所属专业方式变化。";
                            SqlSugarHelper.Db.Insertable(noticeObj).AS(ec_Wire_GroupService.NoticeTableName).ExecuteCommand();
                        }
                    }

                }
                else
                {
                    foreach (var item in entity.ActionHistorys.Where(x => x.ActionType != Action.删除).OrderBy(x => x.ActionTime))
                    {
                        switch (item.ActionType)
                        {
                            case Action.修改:
                                if (!string.IsNullOrEmpty(entity.ChannelID))
                                {
                                    entity.Status = WireGroupStatusEnum.Used;
                                }
                                else
                                {
                                    entity.Status = WireGroupStatusEnum.New;
                                }
                                break;
                            case Action.准备删除:
                                entity.Status = WireGroupStatusEnum.ToDelete;
                                break;

                            case Action.回收站恢复://前端增加action时，同时会把status改为2，因此这里根据channelID判断下具体是2还是3
                            case Action.撤销删除:
                                if (!string.IsNullOrEmpty(entity.ChannelID))
                                {
                                    entity.Status = WireGroupStatusEnum.Used;
                                }
                                else
                                {
                                    entity.Status = WireGroupStatusEnum.New;
                                }
                                break;
                            case Action.关联通道:
                                if (!string.IsNullOrEmpty(entity.ChannelID))
                                {
                                    entity.Status = WireGroupStatusEnum.Used;
                                }
                                else
                                {
                                    continue;
                                }
                                break;
                            case Action.审核:

                                entity.Status = WireGroupStatusEnum.Confirmed;

                                break;
                            case Action.重新打开:

                                entity.Status = WireGroupStatusEnum.Reopen;

                                break;
                            case Action.关联解除:
                                if (string.IsNullOrEmpty(entity.ChannelID))
                                {
                                    entity.Status = WireGroupStatusEnum.New;
                                }
                                else
                                {
                                    continue;
                                }

                                break;
                            default:
                                break;
                        }//根据action，给新的状态

                        if (string.IsNullOrEmpty(ID))
                        {
                            res = ec_Wire_GroupService.Add(entity, ref ID, false);//第一次运行new之后,ID就有值了
                            if (!string.IsNullOrEmpty(res)) return res;
                        }
                        else
                        {
                            if (existObj == null)
                            {
                                res = "数据库中查无此信号。";
                                return res;
                            }
                            else if (existObj.DeleteFlg == true && entity.DeleteFlg == false)
                            {
                                //从回收站恢复
                                ec_Wire_GroupService.Restore(entity, existObj, item.reason, false, MODE);
                            }
                            else if (existObj.Status != WireGroupStatusEnum.ToDelete && entity.Status == WireGroupStatusEnum.ToDelete)
                            {
                                res = ec_Wire_GroupService.ReadyDelete(entity, existObj, item.reason, false, MODE);
                            }
                            else if ((existObj.Status == WireGroupStatusEnum.New || existObj.Status == WireGroupStatusEnum.Used || existObj.Status == WireGroupStatusEnum.Reopen) &&
                                entity.Status == WireGroupStatusEnum.Confirmed)
                            {
                                res = ec_Wire_GroupService.Approve(entity, false, MODE);
                            }
                            else if (existObj.Status == WireGroupStatusEnum.Confirmed && entity.Status == WireGroupStatusEnum.Reopen)
                            {
                                ec_Wire_GroupService.Reopen(entity, existObj, item.reason, false, MODE);
                            }
                            else if (existObj.Status == WireGroupStatusEnum.ToDelete && entity.Status != WireGroupStatusEnum.ToDelete)
                            {
                                ec_Wire_GroupService.UnDoDelete(entity, false, MODE);
                            }
                            else if (existObj.Status == WireGroupStatusEnum.Confirmed && entity.Status == WireGroupStatusEnum.Confirmed)
                            {
                                //无法修改
                                //因此导致客户那边通道迁移出现问题。
                                //客户需要审核后允许通道迁移
                                if (entity.ChannelID != existObj.ChannelID)
                                {
                                    res = ec_Wire_GroupService.MoveToAnotherCh(entity, existObj);
                                }
                            }
                            else
                            {
                                res = ec_Wire_GroupService.Modify(entity, existObj, MODE);
                            }


                            if (!string.IsNullOrEmpty(res))
                            {
                                return res;
                            }
                            if (existObj.ElecOnly != entity.ElecOnly)
                            {
                                var noticeObj = new ec_wire_group_noticeEntity(Action.修改,
                                    ec_Wire_GroupService._Department == Department.电气 ? Department.轮机 : Department.电气, entity.Wire_Group_ID);
                                noticeObj.Create();
                                noticeObj.Message = $"所属专业方式变化。";
                                SqlSugarHelper.Db.Insertable(noticeObj).AS(ec_Wire_GroupService.NoticeTableName).ExecuteCommand();
                            }
                        }
                        var log = new ec_wire_group_logEntity(item.ActionType, entity.Wire_Group_ID, item.ActionTime);//这样能用前端里的时间
                        if (!string.IsNullOrEmpty(item.reason)) { log.ExtraMsg = item.reason; }
                        SqlSugarHelper.Db.Insertable(log).AS(LogTableName).ExecuteCommand();

                    }

                }
                #region 把原本占用那个通道的老信号，给解除
                if (entity.ChannelID != "" && entity.ChannelID != "0")
                {
                    var oldSignalsOnTheChannel = new List<ec_Wire_GroupEntity>();
                    oldSignalsOnTheChannel = existSignals.Where(x => x.ChannelID == entity.ChannelID && x.Wire_Group_ID != entity.Wire_Group_ID).ToList();//这个要再前面，不然新的signal也会被查进去

                    //一个通道下只保留最新的一个信号ID
                    foreach (var oldSignalOnTheChannel in oldSignalsOnTheChannel)
                    {
                        var Id_Exist = oldSignalOnTheChannel.Wire_Group_ID;
                        oldSignalOnTheChannel.ChannelID = "";
                        //oldSignalOnTheChannel.StripID = "";
                        //oldSignalOnTheChannel.PanelID = "";
                        if (oldSignalOnTheChannel.Status == WireGroupStatusEnum.Used)
                        {
                            oldSignalOnTheChannel.Status = WireGroupStatusEnum.New;
                        }
                        ec_Wire_GroupService.SaveEntity(ProjectId, Id_Exist, oldSignalOnTheChannel, MODE);
                    }

                }
                #endregion
                #region 彻底删除信号
                if (entity.DeleteFlg == true && existObj != null)
                {
                    res = ec_Wire_GroupService.Delete(ProjectId, wireGroupId, false);
                    return res;
                }
                #endregion
                return "";
            }
            catch (Exception e)
            {

                throw new Exception(e.Message);
            }

        }

        /// <summary>
        /// 验证是否能修改 或 新建信号
        /// </summary>
        /// <param name="ProjectId"></param>
        /// <param name="entity"></param>
        public string CanSaveEntity(string ProjectId, ec_Wire_GroupEntity entity, Action ActionType)
        {
            try
            {
                string res = "";
                //这里要判断下，因为一个通道下只有一个信号
                //所以如果保存一个新的信号时，要注意把原来的占据这个通道的那个信号的channelID信息给清空掉。
                var existSignals = this.GetList("{ProjectId:\"" + ProjectId + "\"}", false);
                string ID = entity.Wire_Group_ID;
                var existObj = existSignals.FirstOrDefault(x => x.Wire_Group_ID == ID);
                switch (ActionType)
                {
                    case Action.修改:
                        if (!string.IsNullOrEmpty(entity.ChannelID))
                        {
                            entity.Status = WireGroupStatusEnum.Used;
                        }
                        else
                        {
                            entity.Status = WireGroupStatusEnum.New;
                        }
                        break;
                    case Action.准备删除:
                        entity.Status = WireGroupStatusEnum.ToDelete;
                        break;
                    case Action.撤销删除:
                        if (!string.IsNullOrEmpty(entity.ChannelID))
                        {
                            entity.Status = WireGroupStatusEnum.Used;
                        }
                        else
                        {
                            entity.Status = WireGroupStatusEnum.New;
                        }
                        break;
                    case Action.删除:
                        res = ec_Wire_GroupService.Delete(ProjectId, ID, true);
                        return res;
                    case Action.关联通道:
                        if (!string.IsNullOrEmpty(entity.ChannelID))
                        {
                            entity.Status = WireGroupStatusEnum.Used;
                        }
                        break;
                    case Action.审核:

                        entity.Status = WireGroupStatusEnum.Confirmed;

                        break;
                    case Action.重新打开:

                        entity.Status = WireGroupStatusEnum.Reopen;

                        break;
                    case Action.关联解除:
                        if (string.IsNullOrEmpty(entity.ChannelID))
                        {
                            entity.Status = WireGroupStatusEnum.New;
                        }
                        break;
                    default:
                        break;
                }//根据action，给新的状态

                if (string.IsNullOrEmpty(ID))
                {
                    res = ec_Wire_GroupService.Add(entity, ref ID, false, true);//第一次运行new之后,ID就有值了
                    if (!string.IsNullOrEmpty(res)) return res;
                }
                else
                {
                    if (existObj == null)
                    {
                        res = "数据库中查无此信号。";
                        return res;
                    }
                    else if (existObj.Status != WireGroupStatusEnum.ToDelete && entity.Status == WireGroupStatusEnum.ToDelete)
                    {
                        res = ec_Wire_GroupService.ReadyDelete(entity, existObj, " ", false, "0", true);
                    }
                    else if ((existObj.Status == WireGroupStatusEnum.New || existObj.Status == WireGroupStatusEnum.Used || existObj.Status == WireGroupStatusEnum.Reopen) &&
                        entity.Status == WireGroupStatusEnum.Confirmed)
                    {
                        res = ec_Wire_GroupService.Approve(existObj, false, "0", true);
                    }
                    else if (existObj.Status == WireGroupStatusEnum.Confirmed && entity.Status == WireGroupStatusEnum.Reopen)
                    {
                        //ec_Wire_GroupService.Reopen(entity, existObj, " ", false, "0");
                        res = "";
                    }
                    else
                    {
                        res = ec_Wire_GroupService.Modify(entity, existObj, "0", true);
                    }
                    if (!string.IsNullOrEmpty(res))
                    {
                        return res;
                    }
                }
                return "";
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }

        }
        /// <summary>
        /// 读通知
        /// </summary>
        /// <param name="projId"></param>
        /// <param name="NoticeIds"></param>
        /// <returns></returns>
        public List<ec_wire_group_noticeEntity> ReadNotification(string projId, List<string> NoticeIds)
        {
            try
            {
                var NoticeTableName = ProjectSugar.TableName<ec_wire_group_noticeEntity>(projId);
                var res = SqlSugarHelper.Db.Queryable<ec_wire_group_noticeEntity>().AS(NoticeTableName).
                    Where(x => NoticeIds.Contains(x.WireGroupNoticeID)).ToList();

                res.ForEach(x =>
                {
                    x.CheckFLG = true;
                    x.Modify(x.WireGroupNoticeID);
                });

                SqlSugarHelper.Db.Updateable(res).AS(NoticeTableName).
                    UpdateColumns(x => new { x.UpdateUserID, x.UpdateTime, x.CheckFLG }).
                    ExecuteCommand();

                return res;
            }
            catch (Exception e)
            {

                throw new Exception(e.Message);
            }

        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="projectId">项目号，公司级时为空，</param>
        /// <param name="templateId"><see cref="ec_Wire_Group_TemplateEntity.WireGroupTemplateID"/>，项目级时为空</param>
        /// <param name="fileId"></param>
        /// <param name="update"></param>
        /// <param name="version">不同的模板1642,8600等</param>
        /// <returns></returns>
        public string ImportSignals(string projectId, string templateId, string fileId, bool update, int version)
        {
            //获取附件信息

            var file = new AnnexesFileBLL().GetByfolderIdEntity(fileId);
            if (string.IsNullOrWhiteSpace(file?.F_FilePath))
            {
                return "文件不存在";
            }
            if (Path.GetExtension(file.F_FilePath).ToLower() != ".xlsx")
            {
                return "导入文件格式错误，只支持xlsx文件";
            }
            var tableName = ProjectSugar.TableName<ec_Wire_GroupEntity>(projectId);
            var existSignals = SqlSugarHelper.Db.Queryable<ec_Wire_GroupEntity>().AS(tableName)
                .WhereIF(string.IsNullOrEmpty(projectId), x => x.WireGroupTemplateID == templateId).ToList();//公司级时，不同模板之间，是允许重名的
            var templateTableName = ProjectSugar.TableName<ec_Wire_Group_TemplateEntity>(projectId);

            var signals = new List<ec_Wire_GroupEntity>();//保存从excel里读取出来的信号（已经过滤掉了不满足的内容）
            ec_Wire_Group_TemplateEntity template;
            AnnexesFileEntity templateFile;
            #region 1_读取excel
            switch (version)
            {
                case 1:
                    template = SqlSugarHelper.Db.Queryable<ec_Wire_Group_TemplateEntity>(projectId).AS(templateTableName)
                        .First(x => x.TemplateName.Contains("1642"));
                    if (template.IsEmpty())
                    {
                        return "缺少1642的模板文件，请在信号维护界面里面添加";
                    }
                    templateFile = new AnnexesFileBLL().GetByfolderIdEntity(template?.FolderId);
                    signals = ExcelImport1642(file.F_FilePath, existSignals, update, projectId, templateFile?.F_FilePath);
                    break;
                case 2:
                    template = SqlSugarHelper.Db.Queryable<ec_Wire_Group_TemplateEntity>(projectId).AS(templateTableName)
                        .First(x => x.TemplateName.Contains("8600") && !x.TemplateName.Contains("8600Signal"));
                    if (template.IsEmpty())
                    {
                        return "缺少8600的模板文件，请在信号维护界面里面添加";
                    }
                    templateFile = new AnnexesFileBLL().GetByfolderIdEntity(template?.FolderId);
                    signals = ExcelImport8600(file.F_FilePath, existSignals, update, projectId, templateFile?.F_FilePath);
                    break;
                case 3:
                    template = SqlSugarHelper.Db.Queryable<ec_Wire_Group_TemplateEntity>(projectId).AS(templateTableName)
                        .First(x => x.TemplateName.Contains("8600Signal"));
                    if (template.IsEmpty())
                    {
                        return "缺少8600单行版的模板文件，请在信号维护界面里面添加";
                    }
                    templateFile = new AnnexesFileBLL().GetByfolderIdEntity(template?.FolderId);
                    signals = ExcelImport8600SignalRow(file.F_FilePath, existSignals, update, projectId, templateFile?.F_FilePath);
                    break;
            }
            var softTags = new List<ec_Wire_GroupEntity>();
            #endregion
            #region 2_处理数据库
            signals.ForEach(x =>
            {
                x.WireGroupTemplateID = templateId;
                x.NewSoftTags.ForEach(y =>
                {
                    var groupName = x.Group_Name + "_" + y.Code;
                    y.Group_Name = string.IsNullOrEmpty(y.Group_Name) ? groupName : y.Group_Name;
                    y.ParentID = x.Wire_Group_ID;
                    softTags.Add(y);
                });
            });
            try
            {
                SqlSugarHelper.Db.BeginTran();
                SqlSugarHelper.Db.Storageable(signals).As(tableName).ExecuteCommand();
                SqlSugarHelper.Db.Storageable(softTags).As(tableName).ExecuteCommand();
                SqlSugarHelper.Db.CommitTran();
                return "";
            }
            catch (Exception ex)
            {
                SqlSugarHelper.Db.RollbackTran();
                return ex.Message;
            };
            #endregion
        }

        /// <summary>
        /// 24 06 05
        /// </summary>
        /// <param name="projectId">项目号，公司级时为空，</param>
        /// <param name="templateId"><see cref="ec_Wire_Group_TemplateEntity.WireGroupTemplateID"/>，项目级时为空</param>
        /// <param name="fileId"></param>
        /// <param name="mode">1时为（只新增），2时为（新增和更新）,3时为（完全）</param>
        /// <param name="version">不同的模板1642,8600等</param>
        /// <returns></returns>
        public string ImportSignalsFullMode(string projectId, string templateId, string fileId, ImportMode mode, int version)
        {
            //获取附件信息

            var file = new AnnexesFileBLL().GetByfolderIdEntity(fileId);
            if (string.IsNullOrWhiteSpace(file?.F_FilePath))
            {
                return "文件不存在";
            }
            if (Path.GetExtension(file.F_FilePath).ToLower() != ".xlsx")
            {
                return "导入文件格式错误，只支持xlsx文件";
            }
            var tableName = ProjectSugar.TableName<ec_Wire_GroupEntity>(projectId);
            var existSignals = SqlSugarHelper.Db.Queryable<ec_Wire_GroupEntity>().AS(tableName)
                .WhereIF(string.IsNullOrEmpty(projectId), x => x.WireGroupTemplateID == templateId).ToList();//公司级时，不同模板之间，是允许重名的
            var templateTableName = ProjectSugar.TableName<ec_Wire_Group_TemplateEntity>(projectId);

            var signals = new List<ec_Wire_GroupEntity>();//保存从excel里读取出来的信号（已经过滤掉了不满足的内容）
            ec_Wire_Group_TemplateEntity template;
            AnnexesFileEntity templateFile;
            #region 1_读取excel
            switch (version)
            {
                case 1:
                    template = SqlSugarHelper.Db.Queryable<ec_Wire_Group_TemplateEntity>(projectId).AS(templateTableName)
    .First(x => x.TemplateName.Contains("1642"));
                    if (template.IsEmpty())
                    {
                        return "缺少1642的模板文件，请在信号维护界面里面添加";
                    }
                    templateFile = new AnnexesFileBLL().GetByfolderIdEntity(template?.FolderId);
                    if (string.IsNullOrEmpty(templateFile?.F_FilePath))
                    {
                        return "信号模板背后的excel文件不能为空（需要用到里面的Setting页）。";
                    }
                    signals = ExcelImport1642FullMode(file.F_FilePath, existSignals, mode, projectId, templateFile?.F_FilePath);
                    break;
                case 2:
                    template = SqlSugarHelper.Db.Queryable<ec_Wire_Group_TemplateEntity>(projectId).AS(templateTableName)
                        .First(x => x.TemplateName.Contains("8600") && !x.TemplateName.Contains("8600Signal"));
                    if (template.IsEmpty())
                    {
                        return "缺少8600的模板文件，请在信号维护界面里面添加";
                    }
                    templateFile = new AnnexesFileBLL().GetByfolderIdEntity(template?.FolderId);
                    if (string.IsNullOrEmpty(templateFile?.F_FilePath))
                    {
                        return "信号模板背后的excel文件不能为空（需要用到里面的Setting页）。";
                    }
                    signals = ExcelImport8600FullMode(file.F_FilePath, existSignals, mode, projectId, templateFile?.F_FilePath);
                    break;
                case 3:
                    break;
            }
            var softTags = new List<ec_Wire_GroupEntity>();
            #endregion
            #region 2_处理数据库
            signals.ForEach(x =>
            {
                x.WireGroupTemplateID = templateId;
                x.NewSoftTags.ForEach(y =>
                {
                    var groupName = x.Group_Name + "_" + y.Code;
                    y.Group_Name = string.IsNullOrEmpty(y.Group_Name) ? groupName : y.Group_Name;
                    y.ParentID = x.Wire_Group_ID;
                    softTags.Add(y);
                });
            });
            try
            {
                SqlSugarHelper.Db.BeginTran();
                SqlSugarHelper.Db.Storageable(signals).As(tableName).ExecuteCommand();
                SqlSugarHelper.Db.Storageable(softTags).As(tableName).ExecuteCommand();
                SqlSugarHelper.Db.CommitTran(); //数据量太大似乎还有错误。
                return "";
            }
            catch (Exception ex)
            {
                SqlSugarHelper.Db.RollbackTran();
                return ex.Message;
            };
            #endregion
        }

        #endregion
        /// <summary>
        /// 读取excel里面的数据
        /// </summary>
        /// <param name="filePath">文件路径</param>
        /// <param name="existSignals"></param>
        /// <param name="update"></param>
        /// <param name="projectId"></param>
        /// <param name="templatePath"></param>
        /// <returns></returns>
        public List<ec_Wire_GroupEntity> ExcelImport1642(string filePath, List<ec_Wire_GroupEntity> existSignals, bool update, string projectId, string templatePath)
        {
            var signals = new List<ec_Wire_GroupEntity>();//excel里，将要新增的信号
            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
            using (var package = new ExcelPackage(filePath))
            using (var settingPackage = new ExcelPackage(templatePath))
            {
                var sheet = package.Workbook.Worksheets["SPEC"];
                SPEC(sheet, projectId);
                var workSheet = package.Workbook.Worksheets[1];
                var setting = settingPackage.Workbook.Worksheets["Setting"];
                Setting(workSheet, setting);
                ExcelHelper.Clear(workSheet);
                var rg = new Regex("[0-9]{4,5}");
                ec_Wire_GroupEntity signal = null;//也可以用于虚拟点的主信号判断之用
                var lastValidRowNumber = 0; //标记上一个信号的所在行
                bool lastRowIsSoftTag = false; //标记上一行是否为softtag
                for (int rowNumber = 6; rowNumber <= workSheet.Dimension?.End.Row; rowNumber++)
                {
                    string groupName = workSheet.Cells["C" + rowNumber].Text;//C列，CH.NO.
                    string desc = workSheet.Cells["D" + rowNumber].Text.Replace("（", "(").Replace("）", ")").ToUpper();//D列，
                    string code = workSheet.Cells["H" + rowNumber].Text;//H列，
                    var cell = workSheet.Cells["C" + rowNumber];

                    if ((rg.IsMatch(groupName) || groupName.ToUpper().IndexOf("SLD") != -1 || groupName.ToUpper().IndexOf("SHD") != -1) && !desc.IsEmpty())
                    {
                        #region signal行
                        lastRowIsSoftTag = false;
                        lastValidRowNumber = rowNumber;
                        signal = new ec_Wire_GroupEntity();
                        signal.Create();
                        signal.NewSoftTags = new List<ec_Wire_GroupEntity>();
                        var existSignal = existSignals.FirstOrDefault(x => x.Group_Name == groupName);
                        if (existSignal != null)
                        {
                            if (update)
                            {
                                if (existSignal.Status != WireGroupStatusEnum.Confirmed && !existSignal.DeleteFlg)
                                {
                                    signal = existSignal;//允许更新
                                    signal.Modify(existSignal.Wire_Group_ID);
                                }
                                else
                                {
                                    ExcelHelper.AddComment(ref cell, strMessageForConfirmAndDel);
                                    signal = null;
                                }
                            }
                            else
                            {
                                ExcelHelper.AddComment(ref cell, "信号名称在数据库中已存在");//不允许更新，但是系统里已有相同名字的
                                signal = null;
                            }
                        }
                        else
                        {
                            if (update)
                            {
                                ExcelHelper.AddComment(ref cell, "信号名称在数据库中不存在");//只更新，但是系统里没有相同名字的
                                signal = null;
                            }
                        }
                        if (signals.Any(x => x.Group_Name == groupName))
                        {
                            ExcelHelper.AddComment(ref cell, "信号名称在excel里重复");
                            signal = null;
                        }
                        if (signal == null) continue;

                        //要么CH.NO是五位数字，要么是包含SLD
                        signal.Group_Name = groupName;
                        //signal.Group_Desc = desc;//已经转为大写
                        signal.Group_Desc_EN = desc;//已经转为大写                         
                        signal.Code = code;
                        if (!string.IsNullOrEmpty(workSheet.Cells["I" + rowNumber].Text) && workSheet.Cells["I" + rowNumber].Text != "N/A")
                        {
                            signal.IO_Type = workSheet.Cells["I" + rowNumber].Text;
                        }
                        if (!string.IsNullOrEmpty(workSheet.Cells["J" + rowNumber].Text))
                        {
                            signal.Range_Min = workSheet.Cells["J" + rowNumber].Text;
                        }
                        if (!string.IsNullOrEmpty(workSheet.Cells["K" + rowNumber].Text))
                        {
                            signal.Range_Max = workSheet.Cells["K" + rowNumber].Text;
                        }
                        if (!string.IsNullOrEmpty(workSheet.Cells["L" + rowNumber].Text))
                        {
                            signal.Unit = workSheet.Cells["L" + rowNumber].Text;
                        }
                        if (!string.IsNullOrEmpty(workSheet.Cells["M" + rowNumber].Text))
                        {
                            signal.Alarm_LL = workSheet.Cells["M" + rowNumber].Text;
                        }
                        if (!string.IsNullOrEmpty(workSheet.Cells["N" + rowNumber].Text))
                        {
                            signal.Alarm_L = workSheet.Cells["N" + rowNumber].Text;
                        }
                        if (!string.IsNullOrEmpty(workSheet.Cells["O" + rowNumber].Text))
                        {
                            signal.Alarm_H = workSheet.Cells["O" + rowNumber].Text;
                        }
                        if (!string.IsNullOrEmpty(workSheet.Cells["P" + rowNumber].Text))
                        {
                            signal.Alarm_HH = workSheet.Cells["P" + rowNumber].Text;
                        }

                        var sld = workSheet.Cells["Q" + rowNumber].Text;
                        if (!string.IsNullOrEmpty(sld))
                        {
                            signal.SLD = true;
                        }
                        var shd = workSheet.Cells["R" + rowNumber].Text;
                        if (!string.IsNullOrEmpty(shd))
                        {
                            signal.SHD = true;
                        }
                        if (!string.IsNullOrEmpty(workSheet.Cells["S" + rowNumber].Text))
                        {
                            signal.AL_GRP = workSheet.Cells["S" + rowNumber].Text; //不知道为啥原来要| $"|{workSheet.Cells["S" + rowNumber].Text}|"; 
                        }
                        if (!string.IsNullOrEmpty(workSheet.Cells["T" + rowNumber].Text))
                        {
                            signal.BL_GRP = workSheet.Cells["T" + rowNumber].Text; // $"|{workSheet.Cells["T" + rowNumber].Text}|";
                        }
                        if (!string.IsNullOrEmpty(workSheet.Cells["U" + rowNumber].Text))
                        {
                            signal.Time_Delay = workSheet.Cells["U" + rowNumber].Text;
                        }
                        if (!string.IsNullOrEmpty(workSheet.Cells["V" + rowNumber].Text))
                        {
                            signal.Supplier = workSheet.Cells["V" + rowNumber].Text;
                        }
                        if (!string.IsNullOrEmpty(workSheet.Cells["W" + rowNumber].Text))
                        {
                            signal.SENSOR_CODE = workSheet.Cells["W" + rowNumber].Text;
                        }
                        var vdr = workSheet.Cells["X" + rowNumber].Text;
                        if (!string.IsNullOrEmpty(vdr))
                        {
                            signal.VDR_Record = true;
                        }
                        if (!string.IsNullOrEmpty(workSheet.Cells["Y" + rowNumber].Text))
                        {
                            signal.Remarks = workSheet.Cells["Y" + rowNumber].Text;
                        }

                        if (signal.Remarks.ToUpper().Contains("MODBUS") || signal.Remarks.ToUpper().Contains("RS485"))
                        {
                            signal.CommunicationPoint = true;
                            signal.Remarks = signal.Remarks.Replace("MODBUS", "").Replace("RS485", "");
                        }

                        if (rg.IsMatch(groupName))
                        {
                            signal.Signal_Group = groupName.Substring(0, 2);//前2位
                            signal.Signal_SeqNo = groupName.Substring(2);
                        }
                        else if (groupName.ToUpper().StartsWith("SLD"))
                        {
                            signal.Signal_Group = "SLD";//前2位
                            signal.Signal_SeqNo = signal.Group_Name.ToUpper().Replace("SLD", "");
                        }
                        else if (groupName.ToUpper().StartsWith("SHD"))
                        {
                            signal.Signal_Group = "SHD";//前2位
                            signal.Signal_SeqNo = signal.Group_Name.ToUpper().Replace("SHD", "");
                        }
                        signals.Add(signal);
                        ExcelHelper.AddComment(ref cell, "导入为信号", System.Drawing.Color.Green);
                        #endregion
                    }
                    else if (string.IsNullOrEmpty(groupName) && !desc.IsEmpty() && !string.IsNullOrEmpty(code.Trim()) && signal != null)
                    {
                        #region soft tag行

                        var softteg = new ec_Wire_GroupEntity();
                        lastRowIsSoftTag = true;
                        softteg.Create();
                        if (rowNumber - lastValidRowNumber != 1 && !lastRowIsSoftTag)
                        {
                            ExcelHelper.AddComment(ref cell, "虚拟点无法识别，需要紧挨上一个有效的信号行", System.Drawing.Color.AliceBlue);
                            softteg = null;
                            continue;
                        }
                        var existSignal = existSignals.FirstOrDefault(x => x.Group_Name == signal.Group_Name + "_" + code);

                        if (existSignal != null)
                        {
                            if (update)
                            {
                                if (existSignal.Status != WireGroupStatusEnum.Confirmed && !existSignal.DeleteFlg)
                                {
                                    softteg = existSignal;//允许更新
                                    softteg.Modify(existSignal.Wire_Group_ID);
                                }
                                else
                                {
                                    ExcelHelper.AddComment(ref cell, strMessageForConfirmAndDel);
                                    softteg = null;
                                }
                            }
                            else
                            {
                                ExcelHelper.AddComment(ref cell, "虚拟点在数据库中已存在");
                                softteg = null;
                            }
                        }
                        else
                        {
                            if (update)
                            {
                                ExcelHelper.AddComment(ref cell, "虚拟点在数据库中不存在");
                                softteg = null;
                            }
                        }
                        if (signal.NewSoftTags.Any(x => x.Code == code))
                        {
                            ExcelHelper.AddComment(ref cell, "虚拟点在excel里重复");
                            softteg = null;
                        }
                        if (softteg == null) continue;
                        softteg.Group_Name = signal.Group_Name + "_" + code;
                        softteg.Code = code;
                        //softteg.Group_Desc = desc;//已经转为大写
                        softteg.Group_Desc_EN = desc;//已经转为大写

                        if (!string.IsNullOrEmpty(workSheet.Cells["M" + rowNumber].Text))
                        {
                            softteg.Alarm_LL = workSheet.Cells["M" + rowNumber].Text;
                        }
                        if (!string.IsNullOrEmpty(workSheet.Cells["N" + rowNumber].Text))
                        {
                            softteg.Alarm_L = workSheet.Cells["N" + rowNumber].Text;
                        }
                        if (!string.IsNullOrEmpty(workSheet.Cells["O" + rowNumber].Text))
                        {
                            softteg.Alarm_H = workSheet.Cells["O" + rowNumber].Text;
                        }
                        if (!string.IsNullOrEmpty(workSheet.Cells["P" + rowNumber].Text))
                        {
                            softteg.Alarm_HH = workSheet.Cells["P" + rowNumber].Text;
                        }
                        #region SLD,Q
                        var sld = workSheet.Cells["Q" + rowNumber].Text;
                        if (!string.IsNullOrEmpty(sld))
                        {
                            softteg.SLD = true;
                        }
                        #endregion
                        #region SHD,R
                        var shd = workSheet.Cells["R" + rowNumber].Text;
                        if (!string.IsNullOrEmpty(shd))
                        {
                            softteg.SHD = true;
                        }
                        #endregion
                        #region S,延伸报警
                        if (!string.IsNullOrEmpty(workSheet.Cells["S" + rowNumber].Text))
                        {
                            softteg.AL_GRP = workSheet.Cells["S" + rowNumber].Text;
                        }
                        #endregion
                        if (!string.IsNullOrEmpty(workSheet.Cells["T" + rowNumber].Text))
                        {
                            softteg.BL_GRP = workSheet.Cells["T" + rowNumber].Text;
                        }
                        if (!string.IsNullOrEmpty(workSheet.Cells["U" + rowNumber].Text))
                        {
                            softteg.Time_Delay = workSheet.Cells["U" + rowNumber].Text;
                        }
                        var vdr = workSheet.Cells["X" + rowNumber].Text;
                        if (!string.IsNullOrEmpty(vdr))
                        {
                            softteg.VDR_Record = true;
                        }
                        if (!string.IsNullOrEmpty(workSheet.Cells["Y" + rowNumber].Text))
                        {
                            softteg.Remarks = workSheet.Cells["Y" + rowNumber].Text;
                        }
                        softteg.ParentID = signals.Last().Wire_Group_ID;
                        signals.Last().NewSoftTags.Add(softteg);
                        ExcelHelper.AddComment(ref cell, "导入为虚拟点", System.Drawing.Color.LightGreen);
                        #endregion
                    }
                    else
                    {
                        //var cell = workSheet.Cells["C" + rowNumber];
                        ExcelHelper.AddComment(ref cell, "无法识别该行（请检查CH_NO,DESC,CODE等值）");
                    }

                }
                #region 移除掉重复的
                List<string> SLDSignalsNeedRemove = new List<string>();
                foreach (var SLDSignal in signals.Where(x => x.Group_Name.ToUpper().Contains("SLD") || x.Group_Name.ToUpper().Contains("SHD")))
                {
                    //单独提取出的SLD，但是可能已经和某一个拓展点相同了；也可能真的是一个独立的信号
                    //去除SLD SHD后，相同名字的主信号
                    signal = signals.FirstOrDefault(X => SLDSignal.Wire_Group_ID != X.Wire_Group_ID && X.Group_Desc_EN.Trim().Replace(" ", "") == SLDSignal.Group_Desc_EN.Trim().Replace("(SLD)", "").Replace("(SHD)", "").Replace(" ", ""));
                    //比较的时候，应该把现有的信号也一起作为比较
                    if (signal != null)
                    {
                        SLDSignalsNeedRemove.Add(SLDSignal.Wire_Group_ID);
                        //主信号下的某个虚拟点
                        var softTag = signal.NewSoftTags.FirstOrDefault(X => X.Group_Desc_EN.Trim().Replace(" ", "") == SLDSignal.Group_Desc_EN.Trim().Replace(" ", ""));
                        if (softTag != null)
                        {
                            //这个SLD完全就和某一个softtag是一个
                            softTag.Remarks = SLDSignal.Group_Name + " " + softTag.Remarks;
                        }
                        else
                        {
                            //找到了功能一样的主信号，但是主信号下面还没有这个虚拟点，新加到下面
                            signal.NewSoftTags.Add(new ec_Wire_GroupEntity()
                            {
                                Wire_Group_ID = SLDSignal.Wire_Group_ID,
                                Group_Name = signal.Group_Name + "_" + SLDSignal.Code,
                                Group_Desc = SLDSignal.Group_Desc,
                                Group_Desc_EN = SLDSignal.Group_Desc_EN,
                                IO_Type = "",
                                Signal_Group = "",
                                Signal_SeqNo = "",
                                Code = SLDSignal.Code,
                                Range_Min = "",
                                Range_Max = "",
                                Unit = "",
                                Alarm_LL = SLDSignal.Alarm_LL,
                                Alarm_L = SLDSignal.Alarm_L,
                                Alarm_H = SLDSignal.Alarm_H,
                                Alarm_HH = SLDSignal.Alarm_HH,
                                AL_GRP = SLDSignal.AL_GRP,
                                BL_GRP = SLDSignal.BL_GRP,
                                Time_Delay = SLDSignal.Time_Delay,
                                Supplier = "",
                                EquipName = "",
                                SENSOR_CODE = "",
                                VDR_Record = SLDSignal.VDR_Record,
                                SLD = SLDSignal.SLD,
                                SHD = SLDSignal.SHD,
                                Remarks = SLDSignal.Remarks,
                                ParentID = signal.Wire_Group_ID
                            });
                        }
                    }
                    else
                    {
                        //直接去匹配某一个相同名字的虚拟点
                        signal = signals.FirstOrDefault(X => X.NewSoftTags.Any(y => y.Group_Desc_EN.Trim().Replace(" ", "") == SLDSignal.Group_Desc_EN.Trim().Replace(" ", "")));
                        if (signal != null)
                        {
                            SLDSignalsNeedRemove.Add(SLDSignal.Wire_Group_ID);
                            //这个SLD完全就和某一个softtag是一个
                            var softtag = signal.NewSoftTags.FirstOrDefault(y => y.Group_Desc_EN.Trim().Replace(" ", "") == SLDSignal.Group_Desc_EN.Trim().Replace(" ", ""));
                            if (softtag != null)
                            {
                                //这个SLD完全就和某一个softtag是一个
                                softtag.Remarks = SLDSignal.Group_Name + " " + softtag.Remarks;
                            }
                        }
                    }

                }
                #endregion
                package.Save();
                signals.RemoveAll(x => SLDSignalsNeedRemove.Contains(x.Wire_Group_ID));
            }
            return signals;
        }
        /// <summary>
        /// 读取excel里面的数据。且直接在里面更新掉了
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="existSignals">包括仪表和虚拟点</param>
        /// <param name="mode">1时为（只新增），2时为（新增和更新）,3时为（完全）</param>
        /// <param name="projectId"></param>
        /// <param name="templatePath"></param>
        /// <returns></returns>
        public List<ec_Wire_GroupEntity> ExcelImport1642FullMode(string filePath, List<ec_Wire_GroupEntity> existSignals, ImportMode mode, string projectId, string templatePath)
        {
            var signalTableName = ProjectSugar.TableName<ec_Wire_GroupEntity>(projectId);
            var signals = new List<ec_Wire_GroupEntity>();//excel里，将要新增的信号
            var softTags = new List<ec_Wire_GroupEntity>();//excel里，将要新增的虚拟点
            var logs = new List<ec_wire_group_logEntity>();
            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
            if (string.IsNullOrEmpty(filePath))
            {
                throw new ArgumentException("信号导入的Excel文件不能为空。");
            }
            var rg = new Regex("[0-9]{4,5}");
            using (var package = new ExcelPackage(filePath))
            using (var settingPackage = new ExcelPackage(templatePath))
            {
                var sheet = package.Workbook.Worksheets["SPEC"];
                SPEC(sheet, projectId);
                var workSheet = package.Workbook.Worksheets[1];
                var setting = settingPackage.Workbook.Worksheets["Setting"];

                ec_wire_group_logEntity log;
                ec_Wire_GroupEntity signal = null;//也可以用于虚拟点的主信号判断之用
                var lastValidRowNumber = 0; //标记上一个信号的所在行
                bool lastRowIsSoftTag = false; //标记上一行是否为softtag
                ExcelHelper.Clear(workSheet);
                if (workSheet.Dimension == null || workSheet.Dimension.End.Row < 6)
                {
                    return signals;
                }
                bool error = false;
                Setting(workSheet, setting);
                for (int rowNumber = 6; rowNumber <= workSheet.Dimension?.End.Row; rowNumber++)
                {

                    error = false;
                    string groupName = workSheet.Cells["C" + rowNumber].Text;//C列，CH.NO.
                    string desc = workSheet.Cells["D" + rowNumber].Text.Replace("（", "(").Replace("）", ")").ToUpper();//D列，
                    string code = workSheet.Cells["H" + rowNumber].Text;//H列，
                    var cell = workSheet.Cells["C" + rowNumber];
                    var remarkAfterProv = workSheet.Cells["Z" + rowNumber]?.Text + workSheet.Cells["AD" + rowNumber]?.Text;//正常最后一列是Y
                    if ((rg.IsMatch(groupName) || groupName.ToUpper().IndexOf("SLD") != -1 || groupName.ToUpper().IndexOf("SHD") != -1) && !desc.IsEmpty())
                    {

                        #region signal行
                        lastRowIsSoftTag = false;
                        lastValidRowNumber = rowNumber;
                        signal = new ec_Wire_GroupEntity();
                        signal.Create();
                        signal.NewSoftTags = new List<ec_Wire_GroupEntity>();
                        var existSignal = existSignals.FirstOrDefault(x => x.Group_Name.ToUpper() == groupName.ToUpper());
                        if (existSignal != null)
                        {
                            //excel有，数据库也有
                            switch (mode)
                            {
                                case ImportMode.Create:
                                    ExcelHelper.AddComment(ref cell, "信号名称在数据库中已存在");//不允许更新，但是系统里已有相同名字的
                                    error = true;
                                    break;
                                case ImportMode.CreateUpdate:
                                    if (existSignal.Status != WireGroupStatusEnum.Confirmed && !existSignal.DeleteFlg)
                                    {
                                        signal = existSignal;//允许更新
                                        signal.Modify(existSignal.Wire_Group_ID);
                                    }
                                    else
                                    {
                                        ExcelHelper.AddComment(ref cell, strMessageForConfirmAndDel);
                                        error = true;//但是已经审核、删除的不能被编辑
                                    }
                                    break;
                                case ImportMode.Full:
                                    if (workSheet.Cells["C" + rowNumber].Style.Font.Strike == true)//位号所在列，有删除线的标记
                                    {
                                        //有删除线，说明excel里希望删除
                                        if (existSignal.Status != WireGroupStatusEnum.ToDelete || existSignal.Status != WireGroupStatusEnum.deleted || existSignal.DeleteFlg == false)
                                        {
                                            //excel为删除的，数据库里不是删除的                                     
                                            signal = existSignal;
                                            signal.DeleteFlg = true;
                                            signal.Status = WireGroupStatusEnum.deleted;
                                            #region log 
                                            //log还是需要的
                                            log = new ec_wire_group_logEntity(Action.删除, signal.Wire_Group_ID);
                                            log.ExtraMsg = "Excel批量导入时被删除。" + remarkAfterProv;
                                            logs.Add(log);
                                            #endregion
                                            break;
                                        }
                                        else
                                        {
                                            error = true;//都是删除的，不用管了
                                        }
                                    }
                                    else
                                    {
                                        //非删除线，说明excel里认为不需要删除
                                        switch (existSignal.Status)
                                        {
                                            case WireGroupStatusEnum.ToDelete:
                                                signal = existSignal;
                                                if (string.IsNullOrEmpty(signal.ChannelID))
                                                {
                                                    signal.Status = WireGroupStatusEnum.New;
                                                }
                                                else
                                                {
                                                    signal.Status = WireGroupStatusEnum.Used;
                                                }
                                                //通知不需要
                                                #region log 
                                                //log还是需要的
                                                log = new ec_wire_group_logEntity(Action.撤销删除, signal.Wire_Group_ID);
                                                log.ExtraMsg = "Excel批量导入时被修改。" + remarkAfterProv;
                                                logs.Add(log);
                                                #endregion
                                                break;
                                            case WireGroupStatusEnum.New:
                                                signal = existSignal;
                                                #region log 
                                                //log还是需要的
                                                log = new ec_wire_group_logEntity(Action.修改, signal.Wire_Group_ID);
                                                log.ExtraMsg = "Excel批量导入时被修改。" + remarkAfterProv;
                                                logs.Add(log);
                                                #endregion
                                                break;
                                            case WireGroupStatusEnum.Used:
                                                signal = existSignal;
                                                #region log 
                                                //log还是需要的
                                                log = new ec_wire_group_logEntity(Action.修改, signal.Wire_Group_ID);
                                                log.ExtraMsg = "Excel批量导入时被修改。" + remarkAfterProv;
                                                logs.Add(log);
                                                #endregion
                                                break;
                                            case WireGroupStatusEnum.Confirmed://可以更新，但是要记录好属性的变化
                                                signal = existSignal;
                                                #region log 
                                                //log还是需要的
                                                log = new ec_wire_group_logEntity(Action.修改, signal.Wire_Group_ID);
                                                log.ExtraMsg = "Excel批量导入时被修改。" + remarkAfterProv;
                                                logs.Add(log);
                                                #endregion

                                                break;
                                            case WireGroupStatusEnum.Reopen:
                                                signal = existSignal;
                                                #region log 
                                                //log还是需要的
                                                log = new ec_wire_group_logEntity(Action.修改, signal.Wire_Group_ID);
                                                log.ExtraMsg = "Excel批量导入时被修改。" + remarkAfterProv;
                                                logs.Add(log);
                                                #endregion
                                                break;
                                            case WireGroupStatusEnum.deleted:
                                                signal = existSignal;
                                                if (string.IsNullOrEmpty(signal.ChannelID))
                                                {
                                                    signal.Status = WireGroupStatusEnum.New;
                                                }
                                                else
                                                {
                                                    signal.Status = WireGroupStatusEnum.Used;
                                                }
                                                //通知不需要
                                                #region log 
                                                //log还是需要的
                                                log = new ec_wire_group_logEntity(Action.回收站恢复, signal.Wire_Group_ID);
                                                log.ExtraMsg = "Excel批量导入时被修改。" + remarkAfterProv;
                                                logs.Add(log);
                                                #endregion
                                                break;
                                            default:
                                                break;
                                        }

                                        signal.DeleteFlg = false;
                                    }
                                    break;
                                default:
                                    break;
                            }
                        }
                        else
                        {
                            //excel有，数据库没有
                            //需要插入，同时状态为“新增”
                            signal.Status = WireGroupStatusEnum.New;

                            //log还是需要的
                            log = new ec_wire_group_logEntity(Action.新增, signal.Wire_Group_ID);//前面已经.create过了，所以会有ID
                            log.ExtraMsg = "Excel批量导入时新增。" + remarkAfterProv;
                            logs.Add(log);
                            //if (update)
                            //{
                            //    ExcelHelper.AddComment(ref cell, "信号名称在数据库中不存在");//只更新，但是系统里没有相同名字的
                            //    signal = null;
                            //}
                        }
                        if (signals.Any(x => x.Group_Name == groupName))
                        {
                            ExcelHelper.AddComment(ref cell, "信号名称在excel里重复");
                            error = true;
                        }
                        if (error) continue;
                        //-----------------------------------------------------------------------------------------------------属性
                        //要么CH.NO是五位数字，要么是包含SLD
                        #region C,位号
                        signal.Group_Name = groupName;
                        #endregion
                        #region D，描述
                        //signal.Group_Desc = desc;//已经转为大写
                        signal.Group_Desc_EN = desc;//已经转为大写
                        #endregion
                        #region H,code
                        signal.Code = code;
                        #endregion
                        #region I,信号类型
                        if (!string.IsNullOrEmpty(workSheet.Cells["I" + rowNumber].Text) && workSheet.Cells["I" + rowNumber].Text != "N/A")
                        {
                            signal.IO_Type = workSheet.Cells["I" + rowNumber].Text;
                        }
                        #endregion
                        #region JK,范围
                        if (!string.IsNullOrEmpty(workSheet.Cells["J" + rowNumber].Text))
                        {
                            signal.Range_Min = workSheet.Cells["J" + rowNumber].Text;
                        }
                        if (!string.IsNullOrEmpty(workSheet.Cells["K" + rowNumber].Text))
                        {
                            signal.Range_Max = workSheet.Cells["K" + rowNumber].Text;
                        }
                        #endregion
                        #region L，单位
                        if (!string.IsNullOrEmpty(workSheet.Cells["L" + rowNumber].Text))
                        {
                            signal.Unit = workSheet.Cells["L" + rowNumber].Text;
                        }
                        #endregion
                        #region MNOP,报警点
                        if (!string.IsNullOrEmpty(workSheet.Cells["M" + rowNumber].Text))
                        {
                            signal.Alarm_LL = workSheet.Cells["M" + rowNumber].Text;
                        }
                        if (!string.IsNullOrEmpty(workSheet.Cells["N" + rowNumber].Text))
                        {
                            signal.Alarm_L = workSheet.Cells["N" + rowNumber].Text;
                        }
                        if (!string.IsNullOrEmpty(workSheet.Cells["O" + rowNumber].Text))
                        {
                            signal.Alarm_H = workSheet.Cells["O" + rowNumber].Text;
                        }
                        if (!string.IsNullOrEmpty(workSheet.Cells["P" + rowNumber].Text))
                        {
                            signal.Alarm_HH = workSheet.Cells["P" + rowNumber].Text;
                        }
                        #endregion
                        #region Q,sld
                        var sld = workSheet.Cells["Q" + rowNumber].Text;
                        if (!string.IsNullOrEmpty(sld))
                        {
                            signal.SLD = true;
                        }
                        #endregion
                        #region R,SHD
                        var shd = workSheet.Cells["R" + rowNumber].Text;
                        if (!string.IsNullOrEmpty(shd))
                        {
                            signal.SHD = true;
                        }
                        #endregion
                        #region s,延伸报警组
                        if (!string.IsNullOrEmpty(workSheet.Cells["S" + rowNumber].Text))
                        {
                            signal.AL_GRP = workSheet.Cells["S" + rowNumber].Text; //不知道为啥原来要| $"|{workSheet.Cells["S" + rowNumber].Text}|"; 
                        }
                        #endregion
                        #region T,抑制报警
                        if (!string.IsNullOrEmpty(workSheet.Cells["T" + rowNumber].Text))
                        {
                            signal.BL_GRP = workSheet.Cells["T" + rowNumber].Text; // $"|{workSheet.Cells["T" + rowNumber].Text}|";
                        }
                        #endregion
                        #region U,delay
                        if (!string.IsNullOrEmpty(workSheet.Cells["U" + rowNumber].Text))
                        {
                            signal.Time_Delay = workSheet.Cells["U" + rowNumber].Text;
                        }
                        #endregion
                        #region V,maker
                        if (!string.IsNullOrEmpty(workSheet.Cells["V" + rowNumber].Text))
                        {
                            signal.Supplier = workSheet.Cells["V" + rowNumber].Text;
                        }
                        #endregion
                        #region W,传感器
                        if (!string.IsNullOrEmpty(workSheet.Cells["W" + rowNumber].Text))
                        {
                            signal.SENSOR_CODE = workSheet.Cells["W" + rowNumber].Text;
                        }
                        #endregion
                        #region X,VDR
                        var vdr = workSheet.Cells["X" + rowNumber].Text;
                        if (!string.IsNullOrEmpty(vdr))
                        {
                            signal.VDR_Record = true;
                        }
                        #endregion
                        #region Y，备注
                        if (!string.IsNullOrEmpty(workSheet.Cells["Y" + rowNumber].Text))
                        {
                            signal.Remarks = workSheet.Cells["Y" + rowNumber].Text;
                        }
                        #endregion
                        #region 是否为通讯
                        if (signal.Remarks.ToUpper().Contains("MODBUS") || signal.Remarks.ToUpper().Contains("RS485"))
                        {
                            signal.CommunicationPoint = true;
                            signal.Remarks = signal.Remarks.Replace("MODBUS", "").Replace("RS485", "");
                        }
                        #endregion
                        #region C,seqNo和Group分组
                        if (rg.IsMatch(groupName))
                        {
                            signal.Signal_Group = groupName.Substring(0, 2);//前2位
                            signal.Signal_SeqNo = groupName.Substring(2);
                        }
                        else if (groupName.ToUpper().StartsWith("SLD"))
                        {
                            signal.Signal_Group = "SLD";//前2位
                            signal.Signal_SeqNo = signal.Group_Name.ToUpper().Replace("SLD", "");
                        }
                        else if (groupName.ToUpper().StartsWith("SHD"))
                        {
                            signal.Signal_Group = "SHD";//前2位
                            signal.Signal_SeqNo = signal.Group_Name.ToUpper().Replace("SHD", "");
                        }
                        #endregion
                        tryAdd(ref signals, signal);//需要增、改、删（excel里有删除线）
                        ExcelHelper.AddComment(ref cell, "导入为信号", System.Drawing.Color.Green);
                        if (mode == ImportMode.Full && existSignal != null)
                        {
                            switch (existSignal.Status)
                            {
                                case WireGroupStatusEnum.ToDelete:
                                    break;
                                case WireGroupStatusEnum.New:
                                    break;
                                case WireGroupStatusEnum.Used:
                                    break;
                                case WireGroupStatusEnum.Confirmed:
                                    #region 额外的属性log（不能放前面，因为那个时候还没有读取excel里的具体单元格数据）
                                    var ModifyMsg = ec_Wire_GroupService.CompareModifyField(projectId, existSignal, signal, "Status");//信号重新打开
                                    #endregion
                                    break;
                                case WireGroupStatusEnum.Reopen:
                                    break;
                                case WireGroupStatusEnum.deleted:
                                    break;
                                default:
                                    break;
                            }

                        }

                        try
                        {
                            var res = SqlSugarHelper.Db.Storageable(signal).As(signalTableName).ExecuteCommand();
                            if (res == 0)
                            {
                                Console.WriteLine("x");
                            }
                        }
                        catch (Exception ex2)
                        {
                            ExcelHelper.ClearCell(ref cell);
                            ExcelHelper.AddComment(ref cell, "更新至数据库时错误。" + ex2.Message, System.Drawing.Color.OrangeRed);
                        }
                        #endregion
                    }
                    else if (string.IsNullOrEmpty(groupName) && !desc.IsEmpty() && !string.IsNullOrEmpty(code.Trim()) && signal != null)
                    {
                        #region soft tag行
                        var softteg = new ec_Wire_GroupEntity();
                        lastRowIsSoftTag = true;
                        softteg.Create();
                        if (rowNumber - lastValidRowNumber != 1 && !lastRowIsSoftTag)
                        {
                            ExcelHelper.AddComment(ref cell, "虚拟点无法识别，需要紧挨上一个有效的信号行", System.Drawing.Color.AliceBlue);
                            softteg = null;
                            continue;
                        }
                        var softTagName = (signal.Group_Name + "_" + code).ToUpper();
                        var existSignal = existSignals.FirstOrDefault(x => x.Group_Name.ToUpper() == softTagName);

                        if (existSignal != null)
                        {
                            //excel有，数据库也有
                            switch (mode)
                            {
                                case ImportMode.Create:
                                    ExcelHelper.AddComment(ref cell, "虚拟点在数据库中已存在");//不允许更新，但是系统里已有相同名字的
                                    error = true;
                                    break;
                                case ImportMode.CreateUpdate:
                                    if (existSignal.Status != WireGroupStatusEnum.Confirmed && !existSignal.DeleteFlg)
                                    {
                                        softteg = existSignal;//允许更新
                                        softteg.Modify(existSignal.Wire_Group_ID);
                                    }
                                    else
                                    {
                                        ExcelHelper.AddComment(ref cell, strMessageForConfirmAndDel);
                                        error = true;//但是已经审核、删除的不能被编辑
                                    }
                                    break;
                                case ImportMode.Full:
                                    if (workSheet.Cells["D" + rowNumber].Style.Font.Strike == true && workSheet.Cells["H" + rowNumber].Style.Font.Strike == true)//位号没有，所以D和H列，有删除线的标记
                                    {
                                        //有删除线，说明excel里希望删除
                                        if (existSignal.Status != WireGroupStatusEnum.ToDelete || existSignal.Status != WireGroupStatusEnum.deleted || existSignal.DeleteFlg == false)
                                        {
                                            //excel为删除的，数据库里不是删除的                                     
                                            softteg = existSignal;
                                            softteg.DeleteFlg = true;
                                            softteg.Status = WireGroupStatusEnum.deleted;
                                            #region log 
                                            //log还是需要的
                                            log = new ec_wire_group_logEntity(Action.删除, softteg.Wire_Group_ID);
                                            log.ExtraMsg = "Excel批量导入时被删除。" + remarkAfterProv;
                                            logs.Add(log);
                                            #endregion
                                            break;
                                        }
                                        else
                                        {
                                            error = true;//都是删除的，不用管了
                                        }
                                    }
                                    else
                                    {
                                        //非删除线，说明excel里认为不需要删除
                                        switch (existSignal.Status)
                                        {
                                            case WireGroupStatusEnum.ToDelete:
                                                softteg = existSignal;
                                                if (string.IsNullOrEmpty(softteg.ChannelID))
                                                {
                                                    softteg.Status = WireGroupStatusEnum.New;
                                                }
                                                else
                                                {
                                                    softteg.Status = WireGroupStatusEnum.Used;
                                                }
                                                //通知不需要
                                                #region log 
                                                //log还是需要的
                                                log = new ec_wire_group_logEntity(Action.撤销删除, softteg.Wire_Group_ID);
                                                log.ExtraMsg = "Excel批量导入时被修改。" + remarkAfterProv;
                                                logs.Add(log);
                                                #endregion
                                                break;
                                            case WireGroupStatusEnum.New:
                                                softteg = existSignal;
                                                #region log 
                                                //log还是需要的
                                                log = new ec_wire_group_logEntity(Action.修改, softteg.Wire_Group_ID);
                                                log.ExtraMsg = "Excel批量导入时被修改。" + remarkAfterProv;
                                                logs.Add(log);
                                                #endregion
                                                break;
                                            case WireGroupStatusEnum.Used:
                                                softteg = existSignal;
                                                #region log 
                                                //log还是需要的
                                                log = new ec_wire_group_logEntity(Action.修改, softteg.Wire_Group_ID);
                                                log.ExtraMsg = "Excel批量导入时被修改。" + remarkAfterProv;
                                                logs.Add(log);
                                                #endregion
                                                break;
                                            case WireGroupStatusEnum.Confirmed://可以更新，但是要记录好属性的变化
                                                softteg = existSignal;
                                                #region log 
                                                //log还是需要的
                                                log = new ec_wire_group_logEntity(Action.修改, softteg.Wire_Group_ID);
                                                log.ExtraMsg = "Excel批量导入时被修改。" + remarkAfterProv;
                                                logs.Add(log);
                                                #endregion

                                                break;
                                            case WireGroupStatusEnum.Reopen:
                                                softteg = existSignal;
                                                #region log 
                                                //log还是需要的
                                                log = new ec_wire_group_logEntity(Action.修改, softteg.Wire_Group_ID);
                                                log.ExtraMsg = "Excel批量导入时被修改。" + remarkAfterProv;
                                                logs.Add(log);
                                                #endregion
                                                break;
                                            case WireGroupStatusEnum.deleted:
                                                softteg = existSignal;
                                                if (string.IsNullOrEmpty(softteg.ChannelID))
                                                {
                                                    softteg.Status = WireGroupStatusEnum.New;
                                                }
                                                else
                                                {
                                                    softteg.Status = WireGroupStatusEnum.Used;
                                                }
                                                //通知不需要
                                                #region log 
                                                //log还是需要的
                                                log = new ec_wire_group_logEntity(Action.回收站恢复, softteg.Wire_Group_ID);
                                                log.ExtraMsg = "Excel批量导入时被修改。" + remarkAfterProv;
                                                logs.Add(log);
                                                #endregion
                                                break;
                                            default:
                                                break;
                                        }

                                        softteg.DeleteFlg = false;
                                    }
                                    break;
                                default:
                                    break;
                            }
                        }
                        else
                        {
                            //excel有，数据库没有
                            softteg.Status = WireGroupStatusEnum.New;

                            log = new ec_wire_group_logEntity(Action.新增, softteg.Wire_Group_ID);
                            log.ExtraMsg = "Excel批量导入时新增。" + remarkAfterProv;
                            logs.Add(log);
                        }
                        if (signal.NewSoftTags.Any(x => x.Code == code))
                        {
                            ExcelHelper.AddComment(ref cell, "虚拟点在该信号下重复");
                            error = true;
                        }
                        if (error) continue;

                        //----------------------------------------------------------------------------------------------------------
                        #region C,名字
                        softteg.Group_Name = softTagName;
                        #endregion
                        #region D,Description
                        //softteg.Group_Desc = desc;//已经转为大写
                        softteg.Group_Desc_EN = desc;//已经转为大写
                        #endregion                        
                        #region H,code
                        softteg.Code = code;
                        #endregion
                        #region IJKL
                        //不需要，同主仪表
                        #endregion
                        #region MNOP,报警点
                        if (!string.IsNullOrEmpty(workSheet.Cells["M" + rowNumber].Text))
                        {
                            softteg.Alarm_LL = workSheet.Cells["M" + rowNumber].Text;
                        }
                        if (!string.IsNullOrEmpty(workSheet.Cells["N" + rowNumber].Text))
                        {
                            softteg.Alarm_L = workSheet.Cells["N" + rowNumber].Text;
                        }
                        if (!string.IsNullOrEmpty(workSheet.Cells["O" + rowNumber].Text))
                        {
                            softteg.Alarm_H = workSheet.Cells["O" + rowNumber].Text;
                        }
                        if (!string.IsNullOrEmpty(workSheet.Cells["P" + rowNumber].Text))
                        {
                            softteg.Alarm_HH = workSheet.Cells["P" + rowNumber].Text;
                        }
                        #endregion
                        var sld = workSheet.Cells["Q" + rowNumber].Text;
                        if (!string.IsNullOrEmpty(sld))
                        {
                            softteg.SLD = true;
                        }
                        var shd = workSheet.Cells["R" + rowNumber].Text;
                        if (!string.IsNullOrEmpty(shd))
                        {
                            softteg.SHD = true;
                        }
                        if (!string.IsNullOrEmpty(workSheet.Cells["S" + rowNumber].Text))
                        {
                            softteg.AL_GRP = workSheet.Cells["S" + rowNumber].Text;
                        }
                        #region T,抑制报警组
                        if (!string.IsNullOrEmpty(workSheet.Cells["T" + rowNumber].Text))
                        {
                            softteg.BL_GRP = workSheet.Cells["T" + rowNumber].Text;
                        }
                        #endregion
                        #region u,delay
                        if (!string.IsNullOrEmpty(workSheet.Cells["U" + rowNumber].Text))
                        {
                            softteg.Time_Delay = workSheet.Cells["U" + rowNumber].Text;
                        }
                        #endregion
                        #region V,厂商
                        //不需要，同主仪表
                        #endregion
                        #region W,传感器
                        //不需要，同主仪表
                        #endregion
                        #region x,vdr
                        var vdr = workSheet.Cells["X" + rowNumber].Text;
                        if (!string.IsNullOrEmpty(vdr))
                        {
                            softteg.VDR_Record = true;
                        }
                        #endregion
                        #region Y,remark
                        if (!string.IsNullOrEmpty(workSheet.Cells["Y" + rowNumber].Text))
                        {
                            softteg.Remarks = workSheet.Cells["Y" + rowNumber].Text;
                        }
                        #endregion
                        #region 从属关系
                        softteg.ParentID = signals.Last().Wire_Group_ID;
                        signals.Last().NewSoftTags.Add(softteg);
                        #endregion
                        tryAdd(ref softTags, softteg);
                        ExcelHelper.AddComment(ref cell, "导入为虚拟点", System.Drawing.Color.LightGreen);
                        if (mode == ImportMode.Full && existSignal != null)
                        {
                            switch (existSignal.Status)
                            {
                                case WireGroupStatusEnum.ToDelete:
                                    break;
                                case WireGroupStatusEnum.New:
                                    break;
                                case WireGroupStatusEnum.Used:
                                    break;
                                case WireGroupStatusEnum.Confirmed:
                                    #region 额外的属性log（不能放前面，因为那个时候还没有读取excel里的具体单元格数据）
                                    var ModifyMsg = ec_Wire_GroupService.CompareModifyField(projectId, existSignal, softteg, "Status");//虚拟点重新打开
                                    #endregion
                                    break;
                                case WireGroupStatusEnum.Reopen:
                                    break;
                                case WireGroupStatusEnum.deleted:
                                    break;
                                default:
                                    break;
                            }

                        }

                        try
                        {
                            var res = SqlSugarHelper.Db.Storageable(softteg).As(signalTableName).ExecuteCommand();
                            if (res == 0)
                            {
                                Console.WriteLine("x");
                            }
                        }
                        catch (Exception ex2)
                        {
                            ExcelHelper.ClearCell(ref cell);
                            ExcelHelper.AddComment(ref cell, "更新至数据库时错误。" + ex2.Message, System.Drawing.Color.OrangeRed);
                        }
                        #endregion
                    }
                    else
                    {
                        //var cell = workSheet.Cells["C" + rowNumber];
                        ExcelHelper.AddComment(ref cell, "无法识别该行（请检查CH_NO,DESC,CODE等值）");
                        continue;
                    }

                }//循环每一行结束
                #region 移除掉重复的
                List<string> SLDSignalsNeedRemove = new List<string>();
                foreach (var SLDSignal in signals.Where(x => x.Group_Name.ToUpper().Contains("SLD") || x.Group_Name.ToUpper().Contains("SHD")))
                {
                    //单独提取出的SLD，但是可能已经和某一个拓展点相同了；也可能真的是一个独立的信号
                    //去除SLD SHD后，相同名字的主信号
                    signal = signals.FirstOrDefault(X => SLDSignal.Wire_Group_ID != X.Wire_Group_ID && X.Group_Desc_EN.Trim().Replace(" ", "") == SLDSignal.Group_Desc_EN.Trim().Replace("(SLD)", "").Replace("(SHD)", "").Replace(" ", ""));
                    //比较的时候，应该把现有的信号也一起作为比较
                    if (signal != null)
                    {
                        SLDSignalsNeedRemove.Add(SLDSignal.Wire_Group_ID);
                        //主信号下的某个虚拟点
                        var softTag = signal.NewSoftTags.FirstOrDefault(X => X.Group_Desc_EN.Trim().Replace(" ", "") == SLDSignal.Group_Desc_EN.Trim().Replace(" ", ""));
                        if (softTag != null)
                        {
                            //这个SLD完全就和某一个softtag是一个
                            softTag.Remarks = SLDSignal.Group_Name + " " + softTag.Remarks;
                        }
                        else
                        {
                            //找到了功能一样的主信号，但是主信号下面还没有这个虚拟点，新加到下面
                            signal.NewSoftTags.Add(new ec_Wire_GroupEntity()
                            {
                                Wire_Group_ID = SLDSignal.Wire_Group_ID,
                                Group_Name = signal.Group_Name + "_" + SLDSignal.Code,
                                Group_Desc = SLDSignal.Group_Desc,
                                Group_Desc_EN = SLDSignal.Group_Desc_EN,
                                IO_Type = "",
                                Signal_Group = "",
                                Signal_SeqNo = "",
                                Code = SLDSignal.Code,
                                Range_Min = "",
                                Range_Max = "",
                                Unit = "",
                                Alarm_LL = SLDSignal.Alarm_LL,
                                Alarm_L = SLDSignal.Alarm_L,
                                Alarm_H = SLDSignal.Alarm_H,
                                Alarm_HH = SLDSignal.Alarm_HH,
                                AL_GRP = SLDSignal.AL_GRP,
                                BL_GRP = SLDSignal.BL_GRP,
                                Time_Delay = SLDSignal.Time_Delay,
                                Supplier = "",
                                EquipName = "",
                                SENSOR_CODE = "",
                                VDR_Record = SLDSignal.VDR_Record,
                                SLD = SLDSignal.SLD,
                                SHD = SLDSignal.SHD,
                                Remarks = SLDSignal.Remarks,
                                ParentID = signal.Wire_Group_ID
                            });
                        }
                    }
                    else
                    {
                        //直接去匹配某一个相同名字的虚拟点
                        signal = signals.FirstOrDefault(X => X.NewSoftTags.Any(y => y.Group_Desc_EN.Trim().Replace(" ", "") == SLDSignal.Group_Desc_EN.Trim().Replace(" ", "")));
                        if (signal != null)
                        {
                            SLDSignalsNeedRemove.Add(SLDSignal.Wire_Group_ID);
                            //这个SLD完全就和某一个softtag是一个
                            var softtag = signal.NewSoftTags.FirstOrDefault(y => y.Group_Desc_EN.Trim().Replace(" ", "") == SLDSignal.Group_Desc_EN.Trim().Replace(" ", ""));
                            if (softtag != null)
                            {
                                //这个SLD完全就和某一个softtag是一个
                                softtag.Remarks = SLDSignal.Group_Name + " " + softtag.Remarks;
                            }
                        }
                    }

                }
                #endregion

                if (mode == ImportMode.Full)
                {
                    //excel没有，数据库有
                    //本身数据库如果还不是待删除或者已经删除的，就设置为删除
                    var excelSignals = signals.Select(x => x.Group_Name).ToList();
                    //excel没有，数据库有（虚拟点）
                    excelSignals.AddRange(softTags.Select(x => x.Group_Name).ToList());
                    var NeedDeleteSignals = existSignals.Where(x => x.DeleteFlg == false && !excelSignals.Contains(x.Group_Name)).ToList();
                    foreach (var signalToDelete in NeedDeleteSignals)
                    {
                        #region log 
                        //log还是需要的
                        log = new ec_wire_group_logEntity(Action.删除, signalToDelete.Wire_Group_ID);
                        log.ExtraMsg = "Excel批量导入时被删除。";
                        logs.Add(log);
                        #endregion
                        signalToDelete.DeleteFlg = true;
                        signalToDelete.Status = WireGroupStatusEnum.deleted;
                        tryAdd(ref signals, signalToDelete); //需要删（excel里压根没有）

                        var res2 = SqlSugarHelper.Db.Updateable(signalToDelete).AS(signalTableName).ExecuteCommand();
                        if (res2 == 0)
                        {
                            Console.WriteLine("");
                        }
                    }

                

                }
                package.Save();
                //signals.RemoveAll(x => SLDSignalsNeedRemove.Contains(x.Wire_Group_ID));

                string logTableName = ProjectSugar.TableName<ec_wire_group_logEntity>(projectId);
                SqlSugarHelper.Db.Storageable(logs).As(logTableName).ExecuteCommand();//整体更新掉log表
            }
            return new List<ec_Wire_GroupEntity>();//这样外面就不会再更新了，因为这里每次循环后就立马更新掉了。
        }

        /// <summary>
        /// 读取excel里面的数据
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="existSignals"></param>
        /// <param name="update">true时，只进行更新操作</param>
        /// <returns></returns>
        public List<ec_Wire_GroupEntity> ExcelImport8600(string filePath, List<ec_Wire_GroupEntity> existSignals, bool update, string projectId, string templatePath)
        {
            var settingTable = ProjectSugar.TableName<ec_projectSettingsEntity>(projectId);
            var projSettingBll = new ec_projectSettingsBLL();
            var projSettings = projSettingBll.GetEntity("MaxIODesc", projectId);
            if (int.TryParse(projSettings?.SettingValue, out int maxDescLen))
            {
                //根据设置里的来
            }
            else
            {
                maxDescLen = 32;//默认32
            }
            var signals = new List<ec_Wire_GroupEntity>();//excel里处理过的信号
            try
            {
                ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
                if (string.IsNullOrEmpty(filePath))
                {
                    throw new ArgumentException("FilePath cannot be empty");
                }
                if (string.IsNullOrEmpty(templatePath))
                {
                    throw new ArgumentException("TemplatePath cannot be empty");
                }
                using (var package = new ExcelPackage(filePath))
                using (var settingPackage = new ExcelPackage(templatePath))
                {
                    var wss = package.Workbook.Worksheets;

                    SYMB2(wss["SYMB-2"], projectId);//8600
                    var setting = settingPackage.Workbook.Worksheets["Setting"];
                    var rg = new Regex("[0-9]{4,5}");
                    for (int i = 2; i < wss.Count; i++)
                    {
                        var workSheet = wss[i]; //第三个sheet开始
                        if (workSheet.Hidden == eWorkSheetHidden.Hidden || workSheet.Hidden == eWorkSheetHidden.VeryHidden)
                        {
                            continue;//用户手动隐藏的sheet不要
                        }
                        if (workSheet.Name == "FGSS-（参考）") continue;
                        ExcelHelper.Clear(workSheet);
                        if (workSheet.Dimension == null || workSheet.Dimension.End.Row < 6)
                        {
                            continue;
                        }
                        Setting(workSheet, setting);
                        //因为会有空行，得一行一行判断，有信号名称rowNumber再加一
                        for (int rowNumber = 6; rowNumber <= workSheet.Dimension.End.Row; rowNumber = rowNumber + 1)
                        {
                            var signal = new ec_Wire_GroupEntity();  //第六行开始
                            signal.Create();
                            string groupName = workSheet.Cells["A" + rowNumber]?.Text;
                            if (groupName.EndsWith("000")) continue;//说明这行只是一个group而已，并非signal本身
                            bool error = false;
                            var cell = workSheet.Cells["A" + rowNumber];
                            var existSignal = existSignals.FirstOrDefault(x => x.Group_Name == groupName);
                            if (existSignal != null)
                            {
                                if (update)//可以新增和更新
                                {

                                    if (existSignal.Status != WireGroupStatusEnum.Confirmed && !existSignal.DeleteFlg)
                                    {

                                        signal = existSignal;
                                    }
                                    else
                                    {
                                        //但是已经审核、删除的不能被编辑
                                        ExcelHelper.AddComment(ref cell, strMessageForConfirmAndDel);
                                        error = true;
                                    }
                                }
                                else//只能新增
                                {

                                    ExcelHelper.AddComment(ref cell, "信号名称在数据库中已存在");
                                    error = true;
                                }
                            }
                            else
                            {
                                //插入都是允许的
                            }
                            if (signals.Any(x => x.Group_Name == groupName))
                            {
                                ExcelHelper.AddComment(ref cell, "信号名称在excel里重复");
                                error = true;
                            }
                            if (error) continue;

                            cell = workSheet.Cells["B" + rowNumber];
                            string desc = cell?.Text.Replace("（", "(").Replace("）", ")").ToUpper();//B列
                            if (desc.Length > maxDescLen)// 
                            {
                                ExcelHelper.AddComment(ref cell, "超过最大长度限制");
                            }
                            #region signal行
                            if ((rg.IsMatch(groupName) || groupName.ToUpper().IndexOf("SLD") != -1 || groupName.ToUpper().IndexOf("SHD") != -1) && !desc.IsEmpty())
                            {
                                //有效的信号为：
                                //A列的名字符合命名规则
                                //A列的名字不包含SLD SHD
                                //B列的描述不为空

                                //要么CH.NO是五位数字，要么是包含SLD
                                #region A,name
                                signal.Group_Name = groupName;
                                #endregion
                                #region B,描述
                                cell = workSheet.Cells["B" + (rowNumber + 1)];
                                string descCN = cell?.Text.Replace("(", "（").Replace(")", "）").ToUpper();//B列
                                if (descCN.Length > maxDescLen)
                                {
                                    ExcelHelper.AddComment(ref cell, "超过最大长度限制");
                                }

                                signal.Group_Desc = descCN;//已经转为大写
                                signal.Group_Desc_EN = desc;//已经转为大写                 
                                #endregion

                                //LL L H HH报警值
                                string Prow1 = "";
                                string Prow2 = "";
                                string Irow1 = "";
                                string Irow2 = "";
                                string Jrow1 = "";
                                string Jrow2 = "";
                                #region D输入输出
                                signal.InOrOut = workSheet.Cells["D" + rowNumber].Text;
                                #endregion
                                #region EF,io类型
                                signal.IO_Type = workSheet.Cells["E" + rowNumber].Text;
                                if (string.IsNullOrEmpty(signal.IO_Type))
                                {
                                    signal.IO_Type = workSheet.Cells["F" + rowNumber].Text;
                                }
                                #endregion
                                #region G量程
                                var range = workSheet.Cells["G" + rowNumber].Text.Split('~', '-');
                                if (range != null && range.Length == 2)
                                {
                                    signal.Range_Min = range[0];
                                    signal.Range_Max = range[1];
                                }
                                else if (range != null && range.Length == 3)//第一个是负数就刚好不行
                                {
                                    signal.Range_Min = "-" + range[1];
                                    signal.Range_Max = range[2];
                                }
                                #endregion
                                #region H单位
                                signal.Unit = workSheet.Cells["H" + rowNumber].Text;
                                if (signal.Unit == "0C")
                                {
                                    signal.Unit = "℃";
                                }
                                #endregion

                                //23 12 15调整
                                #region K信号编号、通讯点  
                                var code1 = workSheet.Cells["K" + (rowNumber)].GetValue<string>() ?? ""; //K列 第一行
                                var code2 = workSheet.Cells["K" + (rowNumber + 1)].GetValue<string>() ?? ""; //K列 第二行
                                if (code1.ToUpper().Contains("MODBUS") || code1.ToUpper().Contains("485"))
                                {
                                    signal.CommunicationPoint = true;
                                    signal.SENSOR_CODE = code2;
                                }
                                else if (code2.ToUpper().Contains("MODBUS") || code2.ToUpper().Contains("485"))
                                {
                                    signal.SENSOR_CODE = code1;
                                    signal.CommunicationPoint = true;
                                }
                                else
                                {
                                    signal.SENSOR_CODE = code1;
                                    signal.CommunicationPoint = false;
                                }
                                //}

                                #endregion
                                #region L供货商
                                signal.Supplier = workSheet.Cells["L" + rowNumber].Text;
                                #endregion
                                #region M,WH驾驶室
                                string wh = workSheet.Cells["M" + (rowNumber + 1)].Text;
                                if (string.IsNullOrEmpty(wh))
                                {
                                    signal.WHConsole = workSheet.Cells["M" + rowNumber].Text;
                                }
                                else
                                {
                                    signal.WHConsole = $"{workSheet.Cells["M" + rowNumber].Text.Trim()}|{wh}";
                                }
                                #endregion
                                #region N,WH
                                wh = workSheet.Cells["N" + (rowNumber + 1)].Text.Trim();
                                if (string.IsNullOrEmpty(wh))
                                {
                                    signal.WHCPU = workSheet.Cells["N" + rowNumber].Text;
                                }
                                else
                                {
                                    signal.WHCPU = $"{workSheet.Cells["N" + rowNumber].Text.Trim()}|{wh}";
                                }
                                #endregion
                                #region O,ECR集控室
                                string ecr = workSheet.Cells["O" + (rowNumber + 1)].Text;
                                if (string.IsNullOrEmpty(ecr))
                                {
                                    signal.ECRConsole = workSheet.Cells["O" + rowNumber].Text;
                                }
                                else
                                {
                                    signal.ECRConsole = $"{workSheet.Cells["O" + rowNumber].Text}|{ecr}";
                                }
                                #endregion
                                #region P,ECR
                                Prow1 = workSheet.Cells["P" + rowNumber].Text.Trim();
                                Prow2 = workSheet.Cells["P" + (rowNumber + 1)].Text.Trim();

                                if (string.IsNullOrEmpty(Prow2))
                                {
                                    signal.ECRCPU = !string.IsNullOrEmpty(Prow1) ? Prow1 : "";
                                }
                                else
                                {
                                    signal.ECRCPU = $"{Prow1}|{Prow2}"; //UI保存时，也会有|
                                }
                                #endregion
                                #region Q,office办公室
                                string bcc = workSheet.Cells["Q" + (rowNumber + 1)].Text.Trim();
                                if (string.IsNullOrEmpty(bcc))
                                {
                                    signal.ShipOfficeConsole = workSheet.Cells["Q" + rowNumber].Text.Trim();
                                }
                                else
                                {
                                    signal.ShipOfficeConsole = $"{workSheet.Cells["Q" + rowNumber].Text.Trim()}|{bcc}";
                                }
                                #endregion
                                #region R,office
                                bcc = workSheet.Cells["R" + (rowNumber + 1)].Text.Trim();
                                if (string.IsNullOrEmpty(bcc))
                                {
                                    signal.ShipOfficeCPU = workSheet.Cells["R" + rowNumber].Text.Trim();
                                }
                                else
                                {
                                    signal.ShipOfficeCPU = $"{workSheet.Cells["R" + rowNumber].Text.Trim()}|{bcc}";
                                }
                                #endregion
                                #region S,延时
                                signal.Time_Delay = workSheet.Cells["S" + rowNumber].Text;
                                #endregion
                                #region T,抑制
                                signal.BL_GRP = !string.IsNullOrEmpty(workSheet.Cells["T" + rowNumber].Text.Trim()) ? workSheet.Cells["T" + rowNumber].Text.Trim() : "";
                                #endregion
                                signal.VDR_Record = !string.IsNullOrEmpty(workSheet.Cells["U" + rowNumber].Text); //2023 12 26 加
                                #region V,延伸报警组
                                signal.AL_GRP = !string.IsNullOrEmpty(workSheet.Cells["V" + rowNumber].Text.Trim()) ? workSheet.Cells["V" + rowNumber].Text.Trim() : "";
                                #endregion
                                #region W,安全保护和延迟
                                signal.SafetyProt = workSheet.Cells["W" + rowNumber].Text;
                                signal.SafetyDelay = workSheet.Cells["W" + (rowNumber + 1)].Text;
                                if (signal.SafetyProt.Contains("SLD"))
                                {
                                    signal.SLD = true;
                                }
                                if (signal.SafetyProt.Contains("SHD"))
                                {
                                    signal.SHD = true;
                                }
                                #endregion
                                #region X,自动控制
                                signal.AutoCtrl = workSheet.Cells["X" + rowNumber].Text;
                                #endregion
                                #region Y,备注
                                if (workSheet.Cells["Y" + rowNumber].Merge)
                                {
                                    signal.Remarks = workSheet.Cells[workSheet.MergedCells[rowNumber + 1, 25].Split(':')[0]].Text;
                                }
                                else
                                {
                                    signal.Remarks = workSheet.Cells["Y" + rowNumber].Text;
                                }
                                if (signal.Remarks.ToUpper().Contains("MODBUS") || signal.Remarks.ToUpper().Contains("485"))
                                {
                                    signal.CommunicationPoint = true;
                                    signal.Remarks = signal.Remarks.Replace("MODBUS", "").Replace("RS485", "");
                                }
                                #endregion
                                #region IJ,报警连锁，高低共4个内容
                                Irow1 = workSheet.Cells["I" + rowNumber].Text;//报警
                                Irow2 = workSheet.Cells["I" + (rowNumber + 1)].Text;
                                Jrow1 = workSheet.Cells["J" + rowNumber].Text;//停车
                                Jrow2 = workSheet.Cells["J" + (rowNumber + 1)].Text;

                                if (Prow1.EndsWith("I"))//集控室要求的最后一位是I，“显示”
                                {
                                    Prow1 = Prow2;
                                    Prow2 = "";
                                }
                                if (signal.SLD || signal.SHD)//需要前面将SLD或SHD判断好
                                {
                                    if (Prow1.EndsWith("H/L")
                                        || Regex.Match(Prow1, ".AH/.AL").Success)
                                    {
                                        signal.Alarm_HH = Jrow1;
                                        signal.Alarm_LL = Jrow2;
                                    }
                                    else if (Prow1.EndsWith("L/H")
                                        || Regex.Match(Prow1, ".AL/.AH").Success)
                                    {
                                        signal.Alarm_LL = Jrow1;
                                        signal.Alarm_HH = Jrow2;
                                    }
                                    else if (Prow1.EndsWith("AL") && Prow2.EndsWith("AH"))
                                    {
                                        signal.Alarm_LL = Jrow1;
                                        signal.Alarm_HH = Jrow2;
                                    }
                                    else if (Prow1.EndsWith("AH") && Prow2.EndsWith("AL"))
                                    {
                                        signal.Alarm_HH = Jrow1;
                                        signal.Alarm_LL = Jrow2;
                                    }
                                    else if (Prow1.EndsWith("AL"))
                                    {
                                        signal.Alarm_LL = Jrow1;
                                    }
                                    else if (Prow1.EndsWith("AH") || Prow1.EndsWith("AHH"))
                                    {
                                        signal.Alarm_HH = Jrow1;
                                    }
                                }
                                if (Prow1.EndsWith("H/L")
                                    || Regex.Match(Prow1, ".AH/.AL").Success)
                                {
                                    signal.Alarm_H = Irow1;
                                    signal.Alarm_L = Irow2;
                                }
                                else if (Prow1.EndsWith("L/H")
                                    || Regex.Match(Prow1, ".AL/.AH").Success)
                                {
                                    signal.Alarm_L = Irow1;
                                    signal.Alarm_H = Irow2;
                                }
                                else if (Prow1.EndsWith("AL") && Prow2.EndsWith("AH"))
                                {
                                    signal.Alarm_L = Irow1;
                                    signal.Alarm_H = Irow2;
                                }
                                else if (Prow1.EndsWith("AH") && Prow2.EndsWith("AL"))
                                {
                                    signal.Alarm_H = Irow1;
                                    signal.Alarm_L = Irow2;
                                }
                                else if (Prow1.EndsWith("AL") || Prow1.EndsWith("ALL"))
                                {
                                    signal.Alarm_L = Irow1;
                                }
                                else if (Prow1.EndsWith("AH"))
                                {
                                    signal.Alarm_H = Irow1;
                                }
                                else if (Prow1.EndsWith("AHH"))
                                {
                                    signal.Alarm_HH = Jrow1;
                                }
                                else if (Prow1.EndsWith("A"))
                                {
                                    signal.Alarm_H = Irow1;
                                    signal.Alarm_HH = Jrow1;
                                }
                                #endregion

                                #region A,Group,Seq
                                if (rg.IsMatch(groupName))
                                {
                                    signal.Signal_Group = groupName.Substring(0, 2);//前2位
                                    signal.Signal_SeqNo = groupName.Substring(2);
                                }
                                else if (groupName.ToUpper().StartsWith("SLD"))
                                {
                                    signal.Signal_Group = "SLD";//前2位
                                    signal.Signal_SeqNo = signal.Group_Name.ToUpper().Replace("SLD", "");
                                }
                                else if (groupName.ToUpper().StartsWith("SHD"))
                                {
                                    signal.Signal_Group = "SHD";//前2位
                                    signal.Signal_SeqNo = signal.Group_Name.ToUpper().Replace("SHD", "");
                                }
                                #endregion
                                signals.Add(signal);
                                ExcelHelper.AddComment(ref cell, "导入为信号", System.Drawing.Color.Green);
                                rowNumber++;
                            }
                            #endregion
                        }
                    }
                    package.Save();
                }
            }
            catch (ArgumentException ex)
            {
                LogError("ArgumentException: " + ex.Message);
            }
            catch (IOException ex)
            {
                LogError("IOException: " + ex.Message);
            }
            catch (InvalidOperationException ex)
            {
                LogError("InvalidOperationException: " + ex.Message);
            }
            catch (Exception ex)
            {
                LogError("Unhandled Exception: " + ex.Message);
                //log4net.LogManager.GetLogger("ERROR").Error(ex.Message + ex.StackTrace);
            }

            return signals;
        }
        /// <summary>
        /// add时增加一个主键唯一的判断
        /// </summary>
        /// <param name="x"></param>
        void tryAdd(ref List<ec_Wire_GroupEntity> col, ec_Wire_GroupEntity newItem)
        {
            var res = col.Any(x => x.Group_Name == newItem.Group_Name || x.Wire_Group_ID == newItem.Wire_Group_ID);
            if (res)
            {
                //
                Console.WriteLine("x");
            }
            else
            {
                col.Add(newItem);
            }
        }

        /// <summary>
        /// 读取excel里面的数据。且直接在里面更新掉了
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="existSignals"></param>
        /// <param name="mode">1时为（只新增），2时为（新增和更新）,3时为（完全）</param>
        /// <returns></returns>
        public List<ec_Wire_GroupEntity> ExcelImport8600FullMode(string filePath, List<ec_Wire_GroupEntity> existSignals, ImportMode mode, string projectId, string templatePath)
        {
            var settingTable = ProjectSugar.TableName<ec_projectSettingsEntity>(projectId);
            var signalTableName = ProjectSugar.TableName<ec_Wire_GroupEntity>(projectId);
            var projSettingBll = new ec_projectSettingsBLL();
            var projSettings = projSettingBll.GetEntity("MaxIODesc", projectId);
            if (int.TryParse(projSettings?.SettingValue, out int maxDescLen))
            {
                //根据设置里的来
            }
            else
            {
                maxDescLen = 32;//默认32
            }
            var signals = new List<ec_Wire_GroupEntity>();//excel里处理过的信号
            var logs = new List<ec_wire_group_logEntity>();
            ec_wire_group_logEntity log;
            var cursheetandrow = "";
            try
            {
                ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
                if (string.IsNullOrEmpty(filePath))
                {
                    throw new ArgumentException("信号导入的Excel文件不能为空。");
                }
                using (var package = new ExcelPackage(filePath))
                using (var settingPackage = new ExcelPackage(templatePath))
                {
                    var wss = package.Workbook.Worksheets;

                    SYMB2(wss["SYMB-2"], projectId);//8600
                    var setting = settingPackage.Workbook.Worksheets["Setting"];
                    var rg = new Regex("[0-9]{4,5}");
                    for (int i = 2; i < wss.Count; i++)
                    {

                        var workSheet = wss[i]; //第三个sheet开始
                        if (workSheet.Hidden == eWorkSheetHidden.Hidden || workSheet.Hidden == eWorkSheetHidden.VeryHidden)
                        {
                            continue;//用户手动隐藏的sheet不要
                        }
                        if (workSheet.Name == "FGSS-（参考）") continue;
                        ExcelHelper.Clear(workSheet);
                        if (workSheet.Dimension == null || workSheet.Dimension.End.Row < 6)
                        {
                            continue;
                        }
                        Setting(workSheet, setting);
                        //因为会有空行，得一行一行判断，有信号名称rowNumber再加一
                        for (int rowNumber = 6; rowNumber <= workSheet.Dimension.End.Row; rowNumber = rowNumber + 1)
                        {
                            var signal = new ec_Wire_GroupEntity();  //第六行开始
                            signal.Create();
                            string groupName = workSheet.Cells["A" + rowNumber]?.Text;
                            if (groupName.EndsWith("000")) continue;//说明这行只是一个group而已，并非signal本身
                            bool error = false;
                            var cell = workSheet.Cells["A" + rowNumber];
                            cursheetandrow = workSheet.Name + "@" + rowNumber;
                            #region signal行
                            cell = workSheet.Cells["B" + rowNumber];
                            var remarkAfterProv = workSheet.Cells["Z" + rowNumber]?.Text;
                            //送审后修改
                            string desc = cell?.Text.Replace("（", "(").Replace("）", ")").ToUpper();//B列
                            if (desc.Length > maxDescLen)// 
                            {
                                ExcelHelper.AddComment(ref cell, "超过最大长度限制");
                            }
                            if ((rg.IsMatch(groupName) || groupName.ToUpper().IndexOf("SLD") != -1 || groupName.ToUpper().IndexOf("SHD") != -1) && !desc.IsEmpty())
                            {
                                var existSignal = existSignals.FirstOrDefault(x => x.Group_Name.ToUpper() == groupName.ToUpper());

                                if (existSignal != null)
                                {
                                    //excel有，数据库也有
                                    switch (mode)
                                    {
                                        case ImportMode.Create:
                                            ExcelHelper.AddComment(ref cell, "信号名称在数据库中已存在");
                                            error = true;// "信号名称在数据库中已存在"
                                            break;
                                        case ImportMode.CreateUpdate:
                                            if (existSignal.Status != WireGroupStatusEnum.Confirmed && !existSignal.DeleteFlg)
                                            {
                                                signal = existSignal;
                                            }
                                            else
                                            {

                                                ExcelHelper.AddComment(ref cell, strMessageForConfirmAndDel);
                                                error = true;//但是已经审核、删除的不能被编辑
                                            }
                                            break;
                                        case ImportMode.Full:
                                            if (workSheet.Cells["A" + rowNumber].Style.Font.Strike == true)//删除线的标记
                                            {
                                                //有删除线，说明excel里希望删除
                                                if (existSignal.Status != WireGroupStatusEnum.ToDelete || existSignal.Status != WireGroupStatusEnum.deleted || existSignal.DeleteFlg == false)
                                                {
                                                    //excel为删除的，数据库里不是删除的                                     
                                                    signal = existSignal;
                                                    signal.DeleteFlg = true;
                                                    signal.Status = WireGroupStatusEnum.deleted;
                                                    #region log 
                                                    //log还是需要的
                                                    log = new ec_wire_group_logEntity(Action.删除, signal.Wire_Group_ID);
                                                    log.ExtraMsg = "Excel批量导入时被删除。" + remarkAfterProv;
                                                    logs.Add(log);
                                                    #endregion
                                                    break;
                                                }
                                                else
                                                {
                                                    error = true;//都是删除的，不用管了

                                                }
                                            }
                                            else
                                            {
                                                //非删除线，说明excel里认为不需要删除
                                                switch (existSignal.Status)
                                                {
                                                    case WireGroupStatusEnum.ToDelete:
                                                        signal = existSignal;
                                                        if (string.IsNullOrEmpty(signal.ChannelID))
                                                        {
                                                            signal.Status = WireGroupStatusEnum.New;
                                                        }
                                                        else
                                                        {
                                                            signal.Status = WireGroupStatusEnum.Used;
                                                        }
                                                        //通知不需要
                                                        #region log 
                                                        //log还是需要的
                                                        log = new ec_wire_group_logEntity(Action.撤销删除, signal.Wire_Group_ID);
                                                        log.ExtraMsg = "Excel批量导入时被修改。" + remarkAfterProv;
                                                        logs.Add(log);
                                                        #endregion
                                                        break;
                                                    case WireGroupStatusEnum.New:
                                                        signal = existSignal;
                                                        #region log 
                                                        //log还是需要的
                                                        log = new ec_wire_group_logEntity(Action.修改, signal.Wire_Group_ID);
                                                        log.ExtraMsg = "Excel批量导入时被修改。" + remarkAfterProv;
                                                        logs.Add(log);
                                                        #endregion
                                                        break;
                                                    case WireGroupStatusEnum.Used:
                                                        signal = existSignal;
                                                        #region log 
                                                        //log还是需要的
                                                        log = new ec_wire_group_logEntity(Action.修改, signal.Wire_Group_ID);
                                                        log.ExtraMsg = "Excel批量导入时被修改。" + remarkAfterProv;
                                                        logs.Add(log);
                                                        #endregion
                                                        break;
                                                    case WireGroupStatusEnum.Confirmed://可以更新，但是要记录好属性的变化
                                                        signal = existSignal;
                                                        #region log 
                                                        //log还是需要的
                                                        log = new ec_wire_group_logEntity(Action.修改, signal.Wire_Group_ID);
                                                        log.ExtraMsg = "Excel批量导入时被修改。" + remarkAfterProv;
                                                        logs.Add(log);
                                                        #endregion

                                                        break;
                                                    case WireGroupStatusEnum.Reopen:
                                                        signal = existSignal;
                                                        #region log 
                                                        //log还是需要的
                                                        log = new ec_wire_group_logEntity(Action.修改, signal.Wire_Group_ID);
                                                        log.ExtraMsg = "Excel批量导入时被修改。" + remarkAfterProv;
                                                        logs.Add(log);
                                                        #endregion
                                                        break;
                                                    case WireGroupStatusEnum.deleted:
                                                        signal = existSignal;
                                                        if (string.IsNullOrEmpty(signal.ChannelID))
                                                        {
                                                            signal.Status = WireGroupStatusEnum.New;
                                                        }
                                                        else
                                                        {
                                                            signal.Status = WireGroupStatusEnum.Used;
                                                        }
                                                        //通知不需要
                                                        #region log 
                                                        //log还是需要的
                                                        log = new ec_wire_group_logEntity(Action.回收站恢复, signal.Wire_Group_ID);
                                                        log.ExtraMsg = "Excel批量导入时被修改。" + remarkAfterProv;
                                                        logs.Add(log);
                                                        #endregion
                                                        break;
                                                    default:
                                                        break;
                                                }

                                                signal.DeleteFlg = false;
                                            }

                                            break;
                                        default:
                                            break;
                                    }
                                }
                                else
                                {
                                    //excel有，数据库没有
                                    //需要插入，同时状态为“新增”
                                    signal.Status = WireGroupStatusEnum.New;

                                    #region log 
                                    //log还是需要的
                                    log = new ec_wire_group_logEntity(Action.新增, signal.Wire_Group_ID);//前面已经.Create过了，所以有ID
                                    log.ExtraMsg = "Excel批量导入时新增。" + remarkAfterProv;
                                    logs.Add(log);
                                    #endregion
                                }


                                if (signals.Any(x => x.Group_Name == groupName))
                                {
                                    ExcelHelper.AddComment(ref cell, "信号名称在excel里重复");
                                    error = true;//"信号名称在excel里重复"
                                }
                                if (error) continue;


                                //有效的信号为：
                                //A列的名字符合命名规则
                                //A列的名字不包含SLD SHD
                                //B列的描述不为空

                                //要么CH.NO是五位数字，要么是包含SLD
                                #region A,name
                                signal.Group_Name = groupName;
                                #endregion
                                #region B,描述
                                cell = workSheet.Cells["B" + (rowNumber + 1)];
                                string descCN = cell?.Text.Replace("(", "（").Replace(")", "）").ToUpper();//B列
                                if (descCN.Length > maxDescLen)
                                {
                                    ExcelHelper.AddComment(ref cell, "超过最大长度限制");
                                }

                                signal.Group_Desc = descCN;//已经转为大写
                                signal.Group_Desc_EN = desc;//已经转为大写                 
                                #endregion

                                //LL L H HH报警值
                                string Prow1 = "";
                                string Prow2 = "";
                                string Irow1 = "";
                                string Irow2 = "";
                                string Jrow1 = "";
                                string Jrow2 = "";
                                #region D输入输出
                                signal.InOrOut = workSheet.Cells["D" + rowNumber].Text;
                                #endregion
                                #region EF,io类型
                                signal.IO_Type = workSheet.Cells["E" + rowNumber].Text;
                                if (string.IsNullOrEmpty(signal.IO_Type))
                                {
                                    signal.IO_Type = workSheet.Cells["F" + rowNumber].Text;
                                }
                                #endregion
                                #region G量程
                                var range = workSheet.Cells["G" + rowNumber].Text.Split('~', '-');
                                if (range != null && range.Length == 2)
                                {
                                    signal.Range_Min = range[0];
                                    signal.Range_Max = range[1];
                                }
                                else if (range != null && range.Length == 3)//第一个是负数就刚好不行
                                {
                                    signal.Range_Min = "-" + range[1];
                                    signal.Range_Max = range[2];
                                }
                                #endregion
                                #region H单位
                                signal.Unit = workSheet.Cells["H" + rowNumber].Text;
                                if (signal.Unit == "0C")
                                {
                                    signal.Unit = "℃";
                                }
                                #endregion

                                //23 12 15调整
                                #region K信号编号、通讯点  
                                var code1 = workSheet.Cells["K" + (rowNumber)].GetValue<string>() ?? ""; //K列 第一行
                                var code2 = workSheet.Cells["K" + (rowNumber + 1)].GetValue<string>() ?? ""; //K列 第二行
                                if (code1.ToUpper().Contains("MODBUS") || code1.ToUpper().Contains("485"))
                                {
                                    signal.CommunicationPoint = true;
                                    signal.SENSOR_CODE = code2;
                                }
                                else if (code2.ToUpper().Contains("MODBUS") || code2.ToUpper().Contains("485"))
                                {
                                    signal.SENSOR_CODE = code1;
                                    signal.CommunicationPoint = true;
                                }
                                else
                                {
                                    signal.SENSOR_CODE = code1;
                                    signal.CommunicationPoint = false;
                                }
                                //}

                                #endregion
                                #region L供货商
                                signal.Supplier = workSheet.Cells["L" + rowNumber].Text;
                                #endregion
                                #region M,WH驾驶室
                                string wh = workSheet.Cells["M" + (rowNumber + 1)].Text;
                                if (string.IsNullOrEmpty(wh))
                                {
                                    signal.WHConsole = workSheet.Cells["M" + rowNumber].Text;
                                }
                                else
                                {
                                    signal.WHConsole = $"{workSheet.Cells["M" + rowNumber].Text.Trim()}|{wh}";
                                }
                                #endregion
                                #region N,WH
                                wh = workSheet.Cells["N" + (rowNumber + 1)].Text.Trim();
                                if (string.IsNullOrEmpty(wh))
                                {
                                    signal.WHCPU = workSheet.Cells["N" + rowNumber].Text;
                                }
                                else
                                {
                                    signal.WHCPU = $"{workSheet.Cells["N" + rowNumber].Text.Trim()}|{wh}";
                                }
                                #endregion
                                #region O,ECR集控室
                                string ecr = workSheet.Cells["O" + (rowNumber + 1)].Text;
                                if (string.IsNullOrEmpty(ecr))
                                {
                                    signal.ECRConsole = workSheet.Cells["O" + rowNumber].Text;
                                }
                                else
                                {
                                    signal.ECRConsole = $"{workSheet.Cells["O" + rowNumber].Text}|{ecr}";
                                }
                                #endregion
                                #region P,ECR
                                Prow1 = workSheet.Cells["P" + rowNumber].Text.Trim();
                                Prow2 = workSheet.Cells["P" + (rowNumber + 1)].Text.Trim();

                                if (string.IsNullOrEmpty(Prow2))
                                {
                                    signal.ECRCPU = !string.IsNullOrEmpty(Prow1) ? Prow1 : "";
                                }
                                else
                                {
                                    signal.ECRCPU = $"{Prow1}|{Prow2}"; //UI保存时，也会有|
                                }
                                #endregion
                                #region Q,office办公室
                                string bcc = workSheet.Cells["Q" + (rowNumber + 1)].Text.Trim();
                                if (string.IsNullOrEmpty(bcc))
                                {
                                    signal.ShipOfficeConsole = workSheet.Cells["Q" + rowNumber].Text.Trim();
                                }
                                else
                                {
                                    signal.ShipOfficeConsole = $"{workSheet.Cells["Q" + rowNumber].Text.Trim()}|{bcc}";
                                }
                                #endregion
                                #region R,office
                                bcc = workSheet.Cells["R" + (rowNumber + 1)].Text.Trim();
                                if (string.IsNullOrEmpty(bcc))
                                {
                                    signal.ShipOfficeCPU = workSheet.Cells["R" + rowNumber].Text.Trim();
                                }
                                else
                                {
                                    signal.ShipOfficeCPU = $"{workSheet.Cells["R" + rowNumber].Text.Trim()}|{bcc}";
                                }
                                #endregion
                                #region S,延时
                                signal.Time_Delay = workSheet.Cells["S" + rowNumber].Text;
                                #endregion
                                #region T,抑制
                                signal.BL_GRP = !string.IsNullOrEmpty(workSheet.Cells["T" + rowNumber].Text.Trim()) ? workSheet.Cells["T" + rowNumber].Text.Trim() : "";
                                #endregion
                                signal.VDR_Record = !string.IsNullOrEmpty(workSheet.Cells["U" + rowNumber].Text); //2023 12 26 加
                                #region V,延伸报警组
                                signal.AL_GRP = !string.IsNullOrEmpty(workSheet.Cells["V" + rowNumber].Text.Trim()) ? workSheet.Cells["V" + rowNumber].Text.Trim() : "";
                                #endregion
                                #region W,安全保护和延迟
                                signal.SafetyProt = workSheet.Cells["W" + rowNumber].Text;
                                signal.SafetyDelay = workSheet.Cells["W" + (rowNumber + 1)].Text;
                                if (signal.SafetyProt.Contains("SLD"))
                                {
                                    signal.SLD = true;
                                }
                                if (signal.SafetyProt.Contains("SHD"))
                                {
                                    signal.SHD = true;
                                }
                                #endregion
                                #region X,自动控制
                                signal.AutoCtrl = workSheet.Cells["X" + rowNumber].Text;
                                #endregion
                                #region Y,备注
                                if (workSheet.Cells["Y" + rowNumber].Merge)
                                {
                                    signal.Remarks = workSheet.Cells[workSheet.MergedCells[rowNumber + 1, 25].Split(':')[0]].Text;
                                }
                                else
                                {
                                    signal.Remarks = workSheet.Cells["Y" + rowNumber].Text;
                                }
                                if (signal.Remarks.ToUpper().Contains("MODBUS") || signal.Remarks.ToUpper().Contains("485"))
                                {
                                    signal.CommunicationPoint = true;
                                    signal.Remarks = signal.Remarks.Replace("MODBUS", "").Replace("RS485", "");
                                }
                                #endregion
                                #region IJ,报警连锁，高低共4个内容
                                Irow1 = workSheet.Cells["I" + rowNumber].Text;//报警
                                Irow2 = workSheet.Cells["I" + (rowNumber + 1)].Text;
                                Jrow1 = workSheet.Cells["J" + rowNumber].Text;//停车
                                Jrow2 = workSheet.Cells["J" + (rowNumber + 1)].Text;

                                if (Prow1.EndsWith("I"))//集控室要求的最后一位是I，“显示”
                                {
                                    Prow1 = Prow2;
                                    Prow2 = "";
                                }
                                if (signal.SLD || signal.SHD)//需要前面将SLD或SHD判断好
                                {
                                    if (Prow1.EndsWith("H/L")
                                        || Regex.Match(Prow1, ".AH/.AL").Success)
                                    {
                                        signal.Alarm_HH = Jrow1;
                                        signal.Alarm_LL = Jrow2;
                                    }
                                    else if (Prow1.EndsWith("L/H")
                                        || Regex.Match(Prow1, ".AL/.AH").Success)
                                    {
                                        signal.Alarm_LL = Jrow1;
                                        signal.Alarm_HH = Jrow2;
                                    }
                                    else if (Prow1.EndsWith("AL") && Prow2.EndsWith("AH"))
                                    {
                                        signal.Alarm_LL = Jrow1;
                                        signal.Alarm_HH = Jrow2;
                                    }
                                    else if (Prow1.EndsWith("AH") && Prow2.EndsWith("AL"))
                                    {
                                        signal.Alarm_HH = Jrow1;
                                        signal.Alarm_LL = Jrow2;
                                    }
                                    else if (Prow1.EndsWith("AL"))
                                    {
                                        signal.Alarm_LL = Jrow1;
                                    }
                                    else if (Prow1.EndsWith("AH") || Prow1.EndsWith("AHH"))
                                    {
                                        signal.Alarm_HH = Jrow1;
                                    }
                                }
                                if (Prow1.EndsWith("H/L")
                                    || Regex.Match(Prow1, ".AH/.AL").Success)
                                {
                                    signal.Alarm_H = Irow1;
                                    signal.Alarm_L = Irow2;
                                }
                                else if (Prow1.EndsWith("L/H")
                                    || Regex.Match(Prow1, ".AL/.AH").Success)
                                {
                                    signal.Alarm_L = Irow1;
                                    signal.Alarm_H = Irow2;
                                }
                                else if (Prow1.EndsWith("AL") && Prow2.EndsWith("AH"))
                                {
                                    signal.Alarm_L = Irow1;
                                    signal.Alarm_H = Irow2;
                                }
                                else if (Prow1.EndsWith("AH") && Prow2.EndsWith("AL"))
                                {
                                    signal.Alarm_H = Irow1;
                                    signal.Alarm_L = Irow2;
                                }
                                else if (Prow1.EndsWith("AL") || Prow1.EndsWith("ALL"))
                                {
                                    signal.Alarm_L = Irow1;
                                }
                                else if (Prow1.EndsWith("AH"))
                                {
                                    signal.Alarm_H = Irow1;
                                }
                                else if (Prow1.EndsWith("AHH"))
                                {
                                    signal.Alarm_HH = Jrow1;
                                }
                                else if (Prow1.EndsWith("A"))
                                {
                                    signal.Alarm_H = Irow1;
                                    signal.Alarm_HH = Jrow1;
                                }
                                #endregion

                                #region A,Group,Seq
                                if (rg.IsMatch(groupName))
                                {
                                    signal.Signal_Group = groupName.Substring(0, 2);//前2位
                                    signal.Signal_SeqNo = groupName.Substring(2);
                                }
                                else if (groupName.ToUpper().StartsWith("SLD"))
                                {
                                    signal.Signal_Group = "SLD";//前2位
                                    signal.Signal_SeqNo = signal.Group_Name.ToUpper().Replace("SLD", "");
                                }
                                else if (groupName.ToUpper().StartsWith("SHD"))
                                {
                                    signal.Signal_Group = "SHD";//前2位
                                    signal.Signal_SeqNo = signal.Group_Name.ToUpper().Replace("SHD", "");
                                }
                                #endregion
                                tryAdd(ref signals, signal); //需要增、改、删（excel为删除线）
                                ExcelHelper.AddComment(ref cell, "导入为信号", System.Drawing.Color.Green);

                                if (mode == ImportMode.Full && existSignal != null)
                                {
                                    switch (existSignal.Status)
                                    {
                                        case WireGroupStatusEnum.ToDelete:
                                            break;
                                        case WireGroupStatusEnum.New:
                                            break;
                                        case WireGroupStatusEnum.Used:
                                            break;
                                        case WireGroupStatusEnum.Confirmed:
                                            #region 额外的属性log（不能放前面，因为那个时候还没有读取excel里的具体单元格数据）
                                            var ModifyMsg = ec_Wire_GroupService.CompareModifyField(projectId, existSignal, signal, "Status");//信号重新打开
                                            #endregion
                                            break;
                                        case WireGroupStatusEnum.Reopen:
                                            break;
                                        case WireGroupStatusEnum.deleted:
                                            break;
                                        default:
                                            break;
                                    }

                                }
                                rowNumber++;//相当于2行
                                try
                                {
                                    var res = SqlSugarHelper.Db.Storageable(signal).As(signalTableName).ExecuteCommand();
                                    if (res == 0)
                                    {
                                        Console.WriteLine("x");
                                    }
                                }
                                catch (Exception ex2)
                                {
                                    ExcelHelper.ClearCell(ref cell);
                                    ExcelHelper.AddComment(ref cell, "更新至数据库时错误。" + ex2.Message, System.Drawing.Color.OrangeRed);
                                }
                            }
                            #endregion
                        }
                    }

                    if (mode == ImportMode.Full)
                    {
                        //excel没有，数据库有
                        //本身数据库如果还不是待删除或者已经删除的，就设置为删除
                        var excelSignals = signals.Select(x => x.Group_Name).ToList();
                        var NeedDeleteSignals = existSignals.Where(x => x.DeleteFlg == false && !excelSignals.Contains(x.Group_Name)).ToList();
                        foreach (var signal in NeedDeleteSignals)
                        {
                            #region log 
                            //log还是需要的
                            log = new ec_wire_group_logEntity(Action.删除, signal.Wire_Group_ID);
                            log.ExtraMsg = "Excel批量导入时被删除。";
                            logs.Add(log);
                            #endregion
                            signal.DeleteFlg = true;
                            signal.Status = WireGroupStatusEnum.deleted;
                            tryAdd(ref signals, signal); //需要删（excel里压根没有）

                            var res2 = SqlSugarHelper.Db.Updateable(signal).
                                AS(signalTableName).ExecuteCommand();
                            if (res2 == 0)
                            {
                                Console.WriteLine("");
                            }
                        }
                    }
                    package.Save();
                }

                string logTableName = ProjectSugar.TableName<ec_wire_group_logEntity>(projectId);
                SqlSugarHelper.Db.Storageable(logs).As(logTableName).ExecuteCommand();
            }
            catch (ArgumentException ex)
            {
                LogError("ArgumentException: " + ex.Message);
            }
            catch (IOException ex)
            {
                LogError("IOException: " + ex.Message);
            }
            catch (InvalidOperationException ex)
            {
                LogError("InvalidOperationException: " + ex.Message);
            }
            catch (Exception ex)
            {
                LogError("Unhandled Exception: " + ex.Message);
                //log4net.LogManager.GetLogger("ERROR").Error(ex.Message + ex.StackTrace);
            }
            //var temp = signals.Distinct().Count();
            //var temp2 = signals.Select(x=>x.Group_Name).Distinct().Count();
            //var temp3 = signals.Select(x => x.Wire_Group_ID).Distinct().Count();
            return new List<ec_Wire_GroupEntity>();
        }


        void LogError(string errorMessage)
        {
            log4net.LogManager.GetLogger("ERROR").Error(errorMessage);
        }
        /// <summary>
        /// 读取excel里面的数据
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="existSignals"></param>
        /// <param name="update"></param>
        /// <returns></returns>
        public List<ec_Wire_GroupEntity> ExcelImport8600SignalRow(string filePath, List<ec_Wire_GroupEntity> existSignals, bool update, string projectId, string templatePath)
        {
            var signals = new List<ec_Wire_GroupEntity>();
            try
            {
                ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
                using (var package = new ExcelPackage(filePath))
                using (var settingPackage = new ExcelPackage(templatePath))
                {
                    var wss = package.Workbook.Worksheets;

                    SYMB2(wss["SYMB-2"], projectId);
                    var setting = settingPackage.Workbook.Worksheets["Setting"];
                    var rg = new Regex("[0-9]{4,5}");
                    for (int i = 2; i < wss.Count; i++)
                    {
                        var workSheet = wss[i]; //第三个sheet开始
                        if (workSheet.Hidden == eWorkSheetHidden.Hidden || workSheet.Hidden == eWorkSheetHidden.VeryHidden)
                        {
                            continue;
                        }
                        if (workSheet.Name == "FGSS-（参考）") continue;
                        ExcelHelper.Clear(workSheet);
                        if (workSheet.Dimension == null || workSheet.Dimension.End.Row < 6)
                        {
                            continue;//空白页也跳过
                        }
                        Setting(workSheet, setting);
                        //因为会有空行，得一行一行判断，有信号名称rowNumber再加一
                        for (int rowNumber = 6; rowNumber <= workSheet.Dimension.End.Row; rowNumber++)
                        {
                            var signal = new ec_Wire_GroupEntity();  //第六行开始
                            signal.Create();
                            string groupName = workSheet.Cells["A" + rowNumber]?.Text;
                            if (groupName.EndsWith("000")) continue;
                            bool error = false;
                            var cell = workSheet.Cells["A" + rowNumber];
                            var existSignal = existSignals.FirstOrDefault(x => x.Group_Name == groupName);
                            if (existSignal != null)
                            {
                                if (update)
                                {
                                    if (existSignal.Status != WireGroupStatusEnum.Confirmed && !existSignal.DeleteFlg)
                                    {
                                        signal = existSignal;
                                    }
                                    else
                                    {
                                        ExcelHelper.AddComment(ref cell, strMessageForConfirmAndDel);
                                        error = true;
                                    }
                                }
                                else
                                {
                                    ExcelHelper.AddComment(ref cell, "信号名称在数据库中已存在");
                                    error = true;
                                }
                            }
                            if (signals.Any(x => x.Group_Name == groupName))
                            {
                                ExcelHelper.AddComment(ref cell, "信号名称在excel里重复");
                                error = true;
                            }
                            if (error) continue;

                            cell = workSheet.Cells["B" + rowNumber];
                            string desc = cell?.Text.Replace("（", "(").Replace("）", ")").ToUpper();//B列
                            if (desc.Length > 32)
                            {
                                ExcelHelper.AddComment(ref cell, "超过最大长度限制");
                            }
                            #region signal行
                            if ((rg.IsMatch(groupName) || groupName.ToUpper().IndexOf("SLD") != -1 || groupName.ToUpper().IndexOf("SHD") != -1) && !desc.IsEmpty())
                            {
                                signal.Group_Name = groupName; //A
                                signal.Group_Desc_EN = desc;   //B           
                                signal.InOrOut = workSheet.Cells["D" + rowNumber].Text;

                                //LL L H HH报警值
                                string Prow1 = "";
                                string Irow1 = "";
                                string Jrow1 = "";
                                #region IO
                                signal.IO_Type = workSheet.Cells["E" + rowNumber].Text;
                                if (string.IsNullOrEmpty(signal.IO_Type))
                                {
                                    signal.IO_Type = workSheet.Cells["F" + rowNumber].Text;
                                }
                                #endregion
                                var range = workSheet.Cells["G" + rowNumber].Text.Split('~', '-');
                                if (range != null && range.Length == 2)
                                {
                                    signal.Range_Min = range[0];
                                    signal.Range_Max = range[1];
                                }
                                else if (range != null && range.Length == 3)//第一个是负数就刚好不行
                                {
                                    signal.Range_Min = "-" + range[1];
                                    signal.Range_Max = range[2];
                                }
                                signal.Unit = workSheet.Cells["H" + rowNumber].Text;
                                if (signal.Unit == "0C")
                                {
                                    signal.Unit = "℃";
                                }

                                signal.SENSOR_CODE = workSheet.Cells["K" + rowNumber].Text;
                                var Lvalue = workSheet.Cells["L" + rowNumber].Text; //可能是modbus，可能是信号采集箱的名字
                                signal.CommunicationPoint = false;
                                signal.CommunicationPoint = Lvalue.ToUpper().Contains("MODBUS") || Lvalue.ToUpper().Contains("485");

                                signal.Supplier = workSheet.Cells["M" + rowNumber].Text;

                                //因ui保存时，会存为A|B|C这样，所以这里替换为|
                                signal.WHConsole = !string.IsNullOrEmpty(workSheet.Cells["N" + rowNumber].Text) ? workSheet.Cells["N" + rowNumber].Text.Replace(",", "|") : "";
                                signal.WHCPU = !string.IsNullOrEmpty(workSheet.Cells["O" + rowNumber].Text) ? workSheet.Cells["O" + rowNumber].Text.Replace(",", "|") : "";
                                //if (!string.IsNullOrEmpty(signal.WHConsole) || !string.IsNullOrEmpty(signal.WHCPU))
                                //{
                                //    signal.WCC = true;
                                //}


                                signal.ECRConsole = !string.IsNullOrEmpty(workSheet.Cells["P" + rowNumber].Text) ? workSheet.Cells["P" + rowNumber].Text.Replace(",", "|") : "";
                                signal.ECRCPU = !string.IsNullOrEmpty(workSheet.Cells["Q" + rowNumber].Text) ? workSheet.Cells["Q" + rowNumber].Text.Replace(",", "|") : "";
                                Prow1 = signal.ECRCPU;
                                //if (!string.IsNullOrEmpty(signal.ECRConsole) || !string.IsNullOrEmpty(signal.ECRCPU))
                                //{
                                //    signal.ECC = true;
                                //}


                                signal.ShipOfficeConsole = !string.IsNullOrEmpty(workSheet.Cells["R" + rowNumber].Text) ? workSheet.Cells["R" + rowNumber].Text.Replace(",", "|") : "";
                                signal.ShipOfficeCPU = !string.IsNullOrEmpty(workSheet.Cells["S" + rowNumber].Text) ? workSheet.Cells["S" + rowNumber].Text.Replace(",", "|") : "";

                                signal.Time_Delay = workSheet.Cells["T" + rowNumber].Text;
                                signal.BL_GRP = !string.IsNullOrEmpty(workSheet.Cells["U" + rowNumber].Text) ? workSheet.Cells["U" + rowNumber].Text : "";
                                signal.VDR_Record = !string.IsNullOrEmpty(workSheet.Cells["V" + rowNumber].Text); //不为空，则为真
                                signal.AL_GRP = !string.IsNullOrEmpty(workSheet.Cells["W" + rowNumber].Text) ? workSheet.Cells["W" + rowNumber].Text : "";

                                signal.SafetyProt = workSheet.Cells["X" + rowNumber].Text;
                                if (signal.SafetyProt.Contains("SLD"))
                                {
                                    signal.SLD = true;
                                }
                                if (signal.SafetyProt.Contains("SHD"))
                                {
                                    signal.SHD = true;
                                }
                                signal.AutoCtrl = workSheet.Cells["Y" + rowNumber].Text;

                                signal.Remarks = workSheet.Cells["Z" + rowNumber].Text;
                                if (signal.Remarks.ToUpper().Contains("MODBUS") || signal.Remarks.ToUpper().Contains("485"))
                                {
                                    signal.CommunicationPoint = true;
                                    signal.Remarks = signal.Remarks.Replace("MODBUS", "").Replace("RS485", "");
                                }
                                #region ALARM_TRIP
                                Irow1 = workSheet.Cells["I" + rowNumber].Text;//报警 
                                Jrow1 = workSheet.Cells["J" + rowNumber].Text;//停车 

                                if (signal.SLD || signal.SHD)
                                {
                                    if (Prow1.EndsWith("H/L"))
                                    {
                                        signal.Alarm_HH = Jrow1;
                                    }
                                    else if (Prow1.EndsWith("L/H"))
                                    {
                                        signal.Alarm_LL = Jrow1;
                                    }
                                    else if (Prow1.EndsWith("AL"))
                                    {
                                        signal.Alarm_LL = Jrow1;
                                    }
                                    else if (Prow1.EndsWith("AH"))
                                    {
                                        signal.Alarm_HH = Jrow1;
                                    }
                                }
                                if (Prow1.EndsWith("H/L"))
                                {
                                    signal.Alarm_H = Irow1;
                                }
                                else if (Prow1.EndsWith("L/H"))
                                {
                                    signal.Alarm_L = Irow1;
                                }
                                else if (Prow1.EndsWith("AL") || Prow1.EndsWith("ALL"))
                                {
                                    signal.Alarm_L = Irow1;
                                }
                                else if (Prow1.EndsWith("AH") || Prow1.EndsWith("AHH"))
                                {
                                    signal.Alarm_H = Irow1;
                                }
                                else if (Prow1.EndsWith("A"))
                                {
                                    signal.Alarm_H = Irow1;
                                    signal.Alarm_HH = Jrow1;
                                }
                                #endregion


                                if (rg.IsMatch(groupName))
                                {
                                    signal.Signal_Group = groupName.Substring(0, 2);//前2位
                                    signal.Signal_SeqNo = groupName.Substring(2);
                                }
                                else if (groupName.ToUpper().StartsWith("SLD"))
                                {
                                    signal.Signal_Group = "SLD";//前2位
                                    signal.Signal_SeqNo = signal.Group_Name.ToUpper().Replace("SLD", "");
                                }
                                else if (groupName.ToUpper().StartsWith("SHD"))
                                {
                                    signal.Signal_Group = "SHD";//前2位
                                    signal.Signal_SeqNo = signal.Group_Name.ToUpper().Replace("SHD", "");
                                }

                                signals.Add(signal);
                                ExcelHelper.AddComment(ref cell, "导入为信号", System.Drawing.Color.Green);
                            }
                            else
                            {
                                ExcelHelper.AddComment(ref cell, "无法识别该行（请检查CH_NO,DESC,CODE等值）");
                            }
                            #endregion
                        }

                    }

                    package.Save();
                }
            }
            catch (Exception ex)
            {
                log4net.LogManager.GetLogger("ERROR").Error(ex.Message + ex.StackTrace);
            }

            return signals;
        }
        /// <summary>
        /// 导入时根据SPEC增加4个数据字典（信号组别、延伸报警组、延伸报警板、报警阻断组）
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="projectId"></param>
        void SPEC(ExcelWorksheet sheet, string projectId)
        {
            var tableName = ProjectSugar.TableName<ec_dataitemEntity>(projectId);
            var detailTableName = ProjectSugar.TableName<ec_dataitemdetailEntity>(projectId);
            var upDataItem = SqlSugarHelper.Db.Queryable<ec_dataitemEntity>().AS(tableName).First(x => x.DataItemCode == "自动化平台");
            if (upDataItem == null)
            {
                upDataItem = new ec_dataitemEntity();
                upDataItem.Create();
                upDataItem.UpDataItemID = "0";
                upDataItem.DataItemName = "自动化平台";
                upDataItem.DataItemCode = "自动化平台";
                upDataItem.OrderID = 6;
                upDataItem.IsEnabled = 1;
                upDataItem.IsAllowEdit = 1;
                SqlSugarHelper.Db.Insertable(upDataItem).AS(tableName).ExecuteCommand();
            }
            var beSignalGroup = SqlSugarHelper.Db.Queryable<ec_dataitemEntity>().AS(tableName).First(x => x.DataItemCode == "Be_Signal_Group");
            if (beSignalGroup == null)
            {
                beSignalGroup = new ec_dataitemEntity();
                beSignalGroup.Create();
                beSignalGroup.UpDataItemID = upDataItem.DataItemID;
                beSignalGroup.DataItemName = "信号分组";
                beSignalGroup.DataItemCode = "Be_Signal_Group";
                beSignalGroup.OrderID = 1;
                beSignalGroup.IsEnabled = 1;
                beSignalGroup.IsAllowEdit = 1;
                SqlSugarHelper.Db.Insertable(beSignalGroup).AS(tableName).ExecuteCommand();
            }
            var beSignalGroupDetails = SqlSugarHelper.Db.Queryable<ec_dataitemdetailEntity>()
                .AS(detailTableName).Where(x => x.DataItemID == beSignalGroup.DataItemID).ToList();

            var extenAlarmGroup = SqlSugarHelper.Db.Queryable<ec_dataitemEntity>().AS(tableName).First(x => x.DataItemCode == "Exten_Alarm_Group");
            if (extenAlarmGroup == null)
            {
                extenAlarmGroup = new ec_dataitemEntity();
                extenAlarmGroup.Create();
                extenAlarmGroup.UpDataItemID = upDataItem.DataItemID;
                extenAlarmGroup.DataItemName = "延伸报警组";
                extenAlarmGroup.DataItemCode = "Exten_Alarm_Group";
                extenAlarmGroup.OrderID = 2;
                extenAlarmGroup.IsEnabled = 1;
                extenAlarmGroup.IsAllowEdit = 1;
                SqlSugarHelper.Db.Insertable(extenAlarmGroup).AS(tableName).ExecuteCommand();
            }
            var extenAlarmGroupDetails = SqlSugarHelper.Db.Queryable<ec_dataitemdetailEntity>()
                .AS(detailTableName).Where(x => x.DataItemID == extenAlarmGroup.DataItemID).ToList();

            var extensionAlarmPanel = SqlSugarHelper.Db.Queryable<ec_dataitemEntity>().AS(tableName).First(x => x.DataItemCode == "Extension_Alarm_Panel");
            if (extensionAlarmPanel == null)
            {
                extensionAlarmPanel = new ec_dataitemEntity();
                extensionAlarmPanel.Create();
                extensionAlarmPanel.UpDataItemID = upDataItem.DataItemID;
                extensionAlarmPanel.DataItemName = "延伸报警板";
                extensionAlarmPanel.DataItemCode = "Extension_Alarm_Panel";
                extensionAlarmPanel.OrderID = 3;
                extensionAlarmPanel.IsEnabled = 1;
                extensionAlarmPanel.IsAllowEdit = 1;
                SqlSugarHelper.Db.Insertable(extensionAlarmPanel).AS(tableName).ExecuteCommand();
            }
            var extensionAlarmPanelDetails = SqlSugarHelper.Db.Queryable<ec_dataitemdetailEntity>()
                .AS(detailTableName).Where(x => x.DataItemID == extensionAlarmPanel.DataItemID).ToList();

            var alarmBlockingGroup = SqlSugarHelper.Db.Queryable<ec_dataitemEntity>().AS(tableName).First(x => x.DataItemCode == "Alarm_Blocking_Group");
            if (alarmBlockingGroup == null)
            {
                alarmBlockingGroup = new ec_dataitemEntity();
                alarmBlockingGroup.Create();
                alarmBlockingGroup.UpDataItemID = upDataItem.DataItemID;
                alarmBlockingGroup.DataItemName = "报警阻断组";
                alarmBlockingGroup.DataItemCode = "Alarm_Blocking_Group";
                alarmBlockingGroup.OrderID = 4;
                alarmBlockingGroup.IsEnabled = 1;
                alarmBlockingGroup.IsAllowEdit = 1;
                SqlSugarHelper.Db.Insertable(alarmBlockingGroup).AS(tableName).ExecuteCommand();
            }
            if (sheet == null)
            {
                return;
            }
            var alarmBlockingGroupDetails = SqlSugarHelper.Db.Queryable<ec_dataitemdetailEntity>()
                .AS(detailTableName).Where(x => x.DataItemID == alarmBlockingGroup.DataItemID).ToList();
            bool isBeSignalGroup = false;
            bool isExtenAlarmGroup = false;
            bool isExtensionAlarmPanel = false;
            bool isAlarmBlockingGroup = false;
            for (int rowNumber = 50; rowNumber < sheet?.Dimension.End.Row;)
            {
                var A = sheet.Cells["A" + rowNumber].Text;
                if (string.IsNullOrEmpty(A))
                {
                    isBeSignalGroup = false;
                    isExtenAlarmGroup = false;
                    isAlarmBlockingGroup = false;
                }
                else if (A == "The group identification is indicated for each alarm point in the table. ")
                {
                    isBeSignalGroup = true;
                    rowNumber++;
                    continue;
                }
                else if (A == "              1）Alarm groups : (AL. GRP) ")
                {
                    isExtenAlarmGroup = true;
                    rowNumber++;
                    continue;
                }
                else if (A == "            2)       Location of the extension alarm panel & Buzzer ")
                {
                    isExtensionAlarmPanel = true;
                    rowNumber++;
                    continue;
                }
                else if (A == "The duty engineer shall be selected between engineers(C/E, 2nd, 3rd,4th) by the duty")
                {
                    isExtensionAlarmPanel = false;
                }
                else if (A == "function is applied with following conditions. ")
                {
                    isAlarmBlockingGroup = true;
                }
                if (isBeSignalGroup)
                {
                    var a = A.Split(':');
                    if (a.Length == 2)
                    {
                        var detail = beSignalGroupDetails.FirstOrDefault(x => x.DataItemCode == a[0].Trim());
                        if (detail == null)
                        {
                            detail = new ec_dataitemdetailEntity();
                            detail.Create();
                            detail.DataItemID = beSignalGroup.DataItemID;
                            detail.UpDataItemDetailID = "0";
                            detail.DataItemNameEN = a[1].Trim();
                            detail.DataItemCode = a[0].Trim();
                            detail.OrderID = beSignalGroupDetails.Count > 0 ? beSignalGroupDetails.Max(x => x.OrderID) + 1 : 1;
                            detail.IsEnabled = 1;
                            beSignalGroupDetails.Add(detail);
                        }
                    }
                }
                if (isExtenAlarmGroup)
                {
                    var a = A.Split(':');
                    if (a.Length == 2)
                    {
                        var detail = extenAlarmGroupDetails.FirstOrDefault(x => x.DataItemCode == a[0].Trim());
                        if (detail == null)
                        {
                            detail = new ec_dataitemdetailEntity();
                            detail.Create();
                            detail.DataItemID = extenAlarmGroup.DataItemID;
                            detail.UpDataItemDetailID = "0";
                            detail.DataItemNameEN = a[1].Trim();
                            detail.DataItemCode = a[0].Trim();
                            detail.OrderID = extenAlarmGroupDetails.Count > 0 ? extenAlarmGroupDetails.Max(x => x.OrderID) + 1 : 1;
                            detail.IsEnabled = 1;
                            extenAlarmGroupDetails.Add(detail);
                        }
                    }
                }
                if (isExtensionAlarmPanel)
                {
                    var detail = extensionAlarmPanelDetails.FirstOrDefault(x => x.DataItemNameEN == A);
                    if (detail == null)
                    {
                        int? orderId = extensionAlarmPanelDetails.Count > 0 ? extensionAlarmPanelDetails.Max(x => x.OrderID) + 1 : 1;
                        detail = new ec_dataitemdetailEntity();
                        detail.Create();
                        detail.DataItemID = extensionAlarmPanel.DataItemID;
                        detail.UpDataItemDetailID = "0";
                        detail.DataItemNameEN = A;
                        detail.DataItemCode = orderId.ToString().PadLeft(2, '0');
                        detail.OrderID = orderId;
                        detail.IsEnabled = 1;
                        extensionAlarmPanelDetails.Add(detail);
                    }
                }
                if (isAlarmBlockingGroup)
                {
                    var a = A.Split(':');
                    if (a.Length == 2)
                    {
                        var detail = alarmBlockingGroupDetails.FirstOrDefault(x => x.DataItemCode == a[0].Trim());
                        if (detail == null)
                        {
                            detail = new ec_dataitemdetailEntity();
                            detail.Create();
                            detail.DataItemID = alarmBlockingGroup.DataItemID;
                            detail.UpDataItemDetailID = "0";
                            detail.DataItemNameEN = a[1].Trim();
                            detail.DataItemCode = a[0].Trim();
                            detail.OrderID = alarmBlockingGroupDetails.Count > 0 ? alarmBlockingGroupDetails.Max(x => x.OrderID) + 1 : 1;
                            detail.IsEnabled = 1;
                            alarmBlockingGroupDetails.Add(detail);
                        }
                    }
                }
                rowNumber++;
            }
            SqlSugarHelper.Db.Storageable(beSignalGroupDetails).As(detailTableName).ExecuteCommand();
            SqlSugarHelper.Db.Storageable(extenAlarmGroupDetails).As(detailTableName).ExecuteCommand();
            SqlSugarHelper.Db.Storageable(extensionAlarmPanelDetails).As(detailTableName).ExecuteCommand();
            SqlSugarHelper.Db.Storageable(alarmBlockingGroupDetails).As(detailTableName).ExecuteCommand();
        }
        /// <summary>
        /// 导入时更新SYMB-2增加7个数据字典（延伸报警组、传感器类型、报警、控制、指示器、其他、厂商）。
        /// 如果没有这个表就直接退出了，暂时不会报错
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="projectId"></param>
        void SYMB2(ExcelWorksheet sheet, string projectId)
        {
            //1：上部分，延伸报警组、传感器类型、报警
            //2：下部分，控制、指示器、其他、厂商
            int part = 1;

            var tableName = ProjectSugar.TableName<ec_dataitemEntity>(projectId);
            var detailTableName = ProjectSugar.TableName<ec_dataitemdetailEntity>(projectId);
            #region 创建需要的8个字典分类（1个父类，7个具体的子类）
            var upDataItem = SqlSugarHelper.Db.Queryable<ec_dataitemEntity>().AS(tableName).First(x => x.DataItemCode == GlobalObject.dataitemName_SignalManage);
            if (upDataItem == null)
            {
                upDataItem = new ec_dataitemEntity();
                upDataItem.Create();
                upDataItem.UpDataItemID = "0";
                upDataItem.DataItemName = "自动化平台";
                upDataItem.DataItemCode = "自动化平台";
                upDataItem.OrderID = 6;
                upDataItem.IsEnabled = 1;
                upDataItem.IsAllowEdit = 1;
                SqlSugarHelper.Db.Insertable(upDataItem).AS(tableName).ExecuteCommand();
            }
            var extenAlarmGroup = SqlSugarHelper.Db.Queryable<ec_dataitemEntity>().AS(tableName).First(x => x.DataItemCode == "Exten_Alarm_Group");
            if (extenAlarmGroup == null)
            {
                extenAlarmGroup = new ec_dataitemEntity();
                extenAlarmGroup.Create();
                extenAlarmGroup.UpDataItemID = upDataItem.DataItemID;
                extenAlarmGroup.DataItemName = "延伸报警组";
                extenAlarmGroup.DataItemCode = "Exten_Alarm_Group";
                extenAlarmGroup.OrderID = 2;
                extenAlarmGroup.IsEnabled = 1;
                extenAlarmGroup.IsAllowEdit = 1;
                SqlSugarHelper.Db.Insertable(extenAlarmGroup).AS(tableName).ExecuteCommand();
            }
            var extenAlarmGroupDetails = SqlSugarHelper.Db.Queryable<ec_dataitemdetailEntity>()
                .AS(detailTableName).Where(x => x.DataItemID == extenAlarmGroup.DataItemID).ToList();

            var sensorType = SqlSugarHelper.Db.Queryable<ec_dataitemEntity>().AS(tableName).First(x => x.DataItemCode == "Sensor_Type");
            if (sensorType == null)
            {
                sensorType = new ec_dataitemEntity();
                sensorType.Create();
                sensorType.UpDataItemID = upDataItem.DataItemID;
                sensorType.DataItemName = "传感器类型";
                sensorType.DataItemCode = "Sensor_Type";
                sensorType.OrderID = 5;
                sensorType.IsEnabled = 1;
                sensorType.IsAllowEdit = 1;
                SqlSugarHelper.Db.Insertable(sensorType).AS(tableName).ExecuteCommand();
            }
            var sensorTypeDetails = SqlSugarHelper.Db.Queryable<ec_dataitemdetailEntity>()
                .AS(detailTableName).Where(x => x.DataItemID == sensorType.DataItemID).ToList();

            var alarm = SqlSugarHelper.Db.Queryable<ec_dataitemEntity>().AS(tableName).First(x => x.DataItemCode == "Alarm");
            if (alarm == null)
            {
                alarm = new ec_dataitemEntity();
                alarm.Create();
                alarm.UpDataItemID = upDataItem.DataItemID;
                alarm.DataItemName = "报警";
                alarm.DataItemCode = "Alarm";
                alarm.OrderID = 6;
                alarm.IsEnabled = 1;
                alarm.IsAllowEdit = 1;
                SqlSugarHelper.Db.Insertable(alarm).AS(tableName).ExecuteCommand();
            }
            var alarmDetails = SqlSugarHelper.Db.Queryable<ec_dataitemdetailEntity>()
                .AS(detailTableName).Where(x => x.DataItemID == alarm.DataItemID).ToList();

            var control = SqlSugarHelper.Db.Queryable<ec_dataitemEntity>().AS(tableName).First(x => x.DataItemCode == "Control");
            if (control == null)
            {
                control = new ec_dataitemEntity();
                control.Create();
                control.UpDataItemID = upDataItem.DataItemID;
                control.DataItemName = "控制";
                control.DataItemCode = "Control";
                control.OrderID = 7;
                control.IsEnabled = 1;
                control.IsAllowEdit = 1;
                SqlSugarHelper.Db.Insertable(control).AS(tableName).ExecuteCommand();
            }
            var controlDetails = SqlSugarHelper.Db.Queryable<ec_dataitemdetailEntity>()
                .AS(detailTableName).Where(x => x.DataItemID == control.DataItemID).ToList();

            var indicator = SqlSugarHelper.Db.Queryable<ec_dataitemEntity>().AS(tableName).First(x => x.DataItemCode == "Indicator");
            if (indicator == null)
            {
                indicator = new ec_dataitemEntity();
                indicator.Create();
                indicator.UpDataItemID = upDataItem.DataItemID;
                indicator.DataItemName = "指示器";
                indicator.DataItemCode = "Indicator";
                indicator.OrderID = 8;
                indicator.IsEnabled = 1;
                indicator.IsAllowEdit = 1;
                SqlSugarHelper.Db.Insertable(indicator).AS(tableName).ExecuteCommand();
            }
            var indicatorDetails = SqlSugarHelper.Db.Queryable<ec_dataitemdetailEntity>()
                .AS(detailTableName).Where(x => x.DataItemID == indicator.DataItemID).ToList();

            var others = SqlSugarHelper.Db.Queryable<ec_dataitemEntity>().AS(tableName).First(x => x.DataItemCode == "Others");
            if (others == null)
            {
                others = new ec_dataitemEntity();
                others.Create();
                others.UpDataItemID = upDataItem.DataItemID;
                others.DataItemName = "其它";
                others.DataItemCode = "Others";
                others.OrderID = 9;
                others.IsEnabled = 1;
                others.IsAllowEdit = 1;
                SqlSugarHelper.Db.Insertable(others).AS(tableName).ExecuteCommand();
            }
            var othersDetails = SqlSugarHelper.Db.Queryable<ec_dataitemdetailEntity>()
                .AS(detailTableName).Where(x => x.DataItemID == others.DataItemID).ToList();

            var maker = SqlSugarHelper.Db.Queryable<ec_dataitemEntity>().AS(tableName).First(x => x.DataItemCode == "Maker");
            if (maker == null)
            {
                maker = new ec_dataitemEntity();
                maker.Create();
                maker.UpDataItemID = upDataItem.DataItemID;
                maker.DataItemName = "厂商";
                maker.DataItemCode = "Maker";
                maker.OrderID = 10;
                maker.IsEnabled = 1;
                maker.IsAllowEdit = 1;
                SqlSugarHelper.Db.Insertable(maker).AS(tableName).ExecuteCommand();
            }
            var makerDetails = SqlSugarHelper.Db.Queryable<ec_dataitemdetailEntity>()
                .AS(detailTableName).Where(x => x.DataItemID == maker.DataItemID).ToList();
            #endregion
            if (sheet == null)
            {
                return;
            }
            for (int rowNumber = 5; rowNumber < sheet?.Dimension.End.Row;)
            {
                if (sheet.Cells["A" + rowNumber].Text.Replace(" ", "").ToUpper() == "SYMBOLDESCRIPTION")
                {
                    rowNumber += 2;
                    continue;
                }
                if (sheet.Cells["A" + rowNumber].Text.Replace(" ", "").ToUpper() == "符号说明")
                {
                    rowNumber += 1;
                    continue;
                }
                if (sheet.Cells["A" + rowNumber].Text.Replace(" ", "").ToUpper() == "1.")
                {
                    rowNumber += 3;
                    continue;
                }
                if (sheet.Cells["A" + rowNumber].Text.Replace(" ", "").ToUpper() == "4.")
                {
                    part = 2;
                    rowNumber += 3;
                    continue;
                }
                if (part == 1)//第一页
                {
                    var A = sheet.Cells["A" + rowNumber].Text; //第一列
                    var B = sheet.Cells["B" + rowNumber].Text; //描述第一行
                    var B1 = sheet.Cells["B" + (rowNumber + 1)].Text; //描述第二行
                    var C = sheet.Cells["C" + rowNumber].Text; //第二列
                    var D = sheet.Cells["D" + rowNumber].Text;
                    var D1 = sheet.Cells["D" + (rowNumber + 1)].Text;
                    var E = sheet.Cells["E" + rowNumber].Text; //第三列
                    var F = sheet.Cells["F" + rowNumber].Text;
                    var F1 = sheet.Cells["F" + (rowNumber + 1)].Text;
                    var G = sheet.Cells["G" + rowNumber].Text; //第四列
                    var H = sheet.Cells["H" + rowNumber].Text;
                    var H1 = sheet.Cells["H" + (rowNumber + 1)].Text;
                    if (!string.IsNullOrEmpty(A) && !string.IsNullOrEmpty(B) && !string.IsNullOrEmpty(B1))
                    {
                        var detail = extenAlarmGroupDetails.FirstOrDefault(x => x.DataItemCode == A);
                        if (detail == null)
                        {

                            detail = new ec_dataitemdetailEntity();
                            detail.Create();
                            detail.DataItemID = extenAlarmGroup.DataItemID;
                            detail.UpDataItemDetailID = "0";
                            detail.DataItemName = B1;
                            detail.DataItemNameEN = B;
                            detail.DataItemCode = A;
                            detail.OrderID = extenAlarmGroupDetails.Count > 0 ? extenAlarmGroupDetails.Max(x => x.OrderID) + 1 : 1;
                            detail.IsEnabled = 1;
                            extenAlarmGroupDetails.Add(detail);
                        }
                    }
                    if (!string.IsNullOrEmpty(C) && !string.IsNullOrEmpty(D) && !string.IsNullOrEmpty(D1))
                    {
                        var detail = sensorTypeDetails.FirstOrDefault(x => x.DataItemCode == C);
                        if (detail == null)
                        {

                            detail = new ec_dataitemdetailEntity();
                            detail.Create();
                            detail.DataItemID = sensorType.DataItemID;
                            detail.UpDataItemDetailID = "0";
                            detail.DataItemName = D1;
                            detail.DataItemNameEN = D;
                            detail.DataItemCode = C;
                            detail.OrderID = sensorTypeDetails.Count > 0 ? sensorTypeDetails.Max(x => x.OrderID) + 1 : 1;
                            detail.IsEnabled = 1;
                            sensorTypeDetails.Add(detail);
                        }
                    }
                    //下面2个都是alarm组别
                    if (!string.IsNullOrEmpty(E) && !string.IsNullOrEmpty(F) && !string.IsNullOrEmpty(F1))
                    {
                        var detail = alarmDetails.FirstOrDefault(x => x.DataItemCode == E);
                        if (detail == null)
                        {

                            detail = new ec_dataitemdetailEntity();
                            detail.Create();
                            detail.DataItemID = alarm.DataItemID;
                            detail.UpDataItemDetailID = "0";
                            detail.DataItemName = F1;
                            detail.DataItemNameEN = F;
                            detail.DataItemCode = E;
                            detail.OrderID = alarmDetails.Count > 0 ? alarmDetails.Max(x => x.OrderID) + 1 : 1;
                            detail.IsEnabled = 1;
                            alarmDetails.Add(detail);
                        }
                    }
                    if (!string.IsNullOrEmpty(G) && !string.IsNullOrEmpty(H) && !string.IsNullOrEmpty(H1))
                    {
                        var detail = alarmDetails.FirstOrDefault(x => x.DataItemCode == G);
                        if (detail == null)
                        {

                            detail = new ec_dataitemdetailEntity();
                            detail.Create();
                            detail.DataItemID = alarm.DataItemID;
                            detail.UpDataItemDetailID = "0";
                            detail.DataItemName = H1;
                            detail.DataItemNameEN = H;
                            detail.DataItemCode = G;
                            detail.OrderID = alarmDetails.Count > 0 ? alarmDetails.Max(x => x.OrderID) + 1 : 1;
                            detail.IsEnabled = 1;
                            alarmDetails.Add(detail);
                        }
                    }
                }
                if (part == 2)//第二页
                {
                    var A = sheet.Cells["A" + rowNumber].Text;
                    var B = sheet.Cells["B" + rowNumber].Text;
                    var B1 = sheet.Cells["B" + (rowNumber + 1)].Text;
                    var C = sheet.Cells["C" + rowNumber].Text;
                    var D = sheet.Cells["D" + rowNumber].Text;
                    var D1 = sheet.Cells["D" + (rowNumber + 1)].Text;
                    var E = sheet.Cells["E" + rowNumber].Text;
                    var F = sheet.Cells["F" + rowNumber].Text;
                    var F1 = sheet.Cells["F" + (rowNumber + 1)].Text;
                    var G = sheet.Cells["G" + rowNumber].Text;
                    var H = sheet.Cells["H" + rowNumber].Text;
                    var H1 = sheet.Cells["H" + (rowNumber + 1)].Text;
                    if (!string.IsNullOrEmpty(A) && !string.IsNullOrEmpty(B) && !string.IsNullOrEmpty(B1))
                    {
                        var detail = controlDetails.FirstOrDefault(x => x.DataItemCode == A);
                        if (detail == null)
                        {

                            detail = new ec_dataitemdetailEntity();
                            detail.Create();
                            detail.DataItemID = control.DataItemID;
                            detail.UpDataItemDetailID = "0";
                            detail.DataItemName = B1;
                            detail.DataItemNameEN = B;
                            detail.DataItemCode = A;
                            detail.OrderID = controlDetails.Count > 0 ? controlDetails.Max(x => x.OrderID) + 1 : 1;
                            detail.IsEnabled = 1;
                            controlDetails.Add(detail);
                        }
                    }
                    if (!string.IsNullOrEmpty(C) && !string.IsNullOrEmpty(D) && !string.IsNullOrEmpty(D1))
                    {
                        var detail = indicatorDetails.FirstOrDefault(x => x.DataItemCode == C);
                        if (detail == null)
                        {

                            detail = new ec_dataitemdetailEntity();
                            detail.Create();
                            detail.DataItemID = indicator.DataItemID;
                            detail.UpDataItemDetailID = "0";
                            detail.DataItemName = D1;
                            detail.DataItemNameEN = D;
                            detail.DataItemCode = C;
                            detail.OrderID = indicatorDetails.Count > 0 ? indicatorDetails.Max(x => x.OrderID) + 1 : 1;
                            detail.IsEnabled = 1;
                            indicatorDetails.Add(detail);
                        }
                    }
                    if (!string.IsNullOrEmpty(E) && !string.IsNullOrEmpty(F) && !string.IsNullOrEmpty(F1))
                    {
                        var detail = othersDetails.FirstOrDefault(x => x.DataItemCode == E);
                        if (detail == null)
                        {

                            detail = new ec_dataitemdetailEntity();
                            detail.Create();
                            detail.DataItemID = others.DataItemID;
                            detail.UpDataItemDetailID = "0";
                            detail.DataItemName = F1;
                            detail.DataItemNameEN = F;
                            detail.DataItemCode = E;
                            detail.OrderID = othersDetails.Count > 0 ? othersDetails.Max(x => x.OrderID) + 1 : 1;
                            detail.IsEnabled = 1;
                            othersDetails.Add(detail);
                        }
                    }
                    //厂商组别
                    if (!string.IsNullOrEmpty(G) && !string.IsNullOrEmpty(H) && !string.IsNullOrEmpty(H1))
                    {
                        var detail = makerDetails.FirstOrDefault(x => x.DataItemCode == G);
                        if (detail == null)
                        {

                            detail = new ec_dataitemdetailEntity();
                            detail.Create();
                            detail.DataItemID = maker.DataItemID;
                            detail.UpDataItemDetailID = "0";
                            detail.DataItemName = H1;
                            detail.DataItemNameEN = H;
                            detail.DataItemCode = G;
                            detail.OrderID = makerDetails.Count > 0 ? makerDetails.Max(x => x.OrderID) + 1 : 1;
                            detail.IsEnabled = 1;
                            makerDetails.Add(detail);
                        }
                    }
                }
                rowNumber += 3;//三行一循环
            }
            //批量存储
            SqlSugarHelper.Db.Storageable(extenAlarmGroupDetails).As(detailTableName).ExecuteCommand();
            SqlSugarHelper.Db.Storageable(sensorTypeDetails).As(detailTableName).ExecuteCommand();
            SqlSugarHelper.Db.Storageable(alarmDetails).As(detailTableName).ExecuteCommand();
            SqlSugarHelper.Db.Storageable(controlDetails).As(detailTableName).ExecuteCommand();
            SqlSugarHelper.Db.Storageable(indicatorDetails).As(detailTableName).ExecuteCommand();
            SqlSugarHelper.Db.Storageable(othersDetails).As(detailTableName).ExecuteCommand();
            SqlSugarHelper.Db.Storageable(makerDetails).As(detailTableName).ExecuteCommand();
        }
        public byte[] ExportSignals(string projectId, bool delete, int version, string folderId)
        {
            var file = new AnnexesFileBLL().GetByfolderIdEntity(folderId);
            if (string.IsNullOrWhiteSpace(file?.F_FilePath))
            {
                return System.Text.Encoding.UTF8.GetBytes("lr_base_annexesfile表中，文件地址F_FilePath为空。请尝试重新上传下模板。");
            }

            var tableName = ProjectSugar.TableName<ec_Wire_GroupEntity>(projectId);
            //var softtagTableName = ProjectSugar.TableName<ec_SofttagEntity>(projectId);
            var itemTableName = ProjectSugar.TableName<ec_dataitemEntity>(projectId);
            var detailTableName = ProjectSugar.TableName<ec_dataitemdetailEntity>(projectId);

            var signals = this.GetList("{ProjectId:\"" + projectId + "\"}", false, true, delete)
                .OrderBy(x => x.Group_Name)
                .ToList();

            var groups = signals.Select(x => x.Signal_Group).Distinct().ToList();

            var details = SqlSugarHelper.Db.Queryable<ec_dataitemdetailEntity>().AS(detailTableName)
                .InnerJoin<ec_dataitemEntity>((a, b) => a.DataItemID == b.DataItemID).AS<ec_dataitemEntity>(itemTableName)
                .Where((a, b) => groups.Contains(a.DataItemCode) && b.DataItemCode == "Be_Signal_Group" && !string.IsNullOrEmpty(a.DataItemCode)) //因为需要靠code的名字来生成worksheet，所以不能为空
                .OrderBy(a => a.DataItemCode)
                .Select((a, b) => a).ToList();

            details.ForEach(x =>
            {
                x.Signals = signals.Where(y => y.Signal_Group == x.DataItemCode).ToList();
            });

            var symb2 = new List<string>() { "Exten_Alarm_Group", "Sensor_Type", "Alarm", "Control", "Indicator", "Others", "Maker",
                "Extension_Alarm_Panel", "Extension_Alarm_Panel", "Alarm_Blocking_Group"};
            var items = SqlSugarHelper.Db.Queryable<ec_dataitemdetailEntity>().AS(detailTableName)
                .InnerJoin<ec_dataitemEntity>((a, b) => a.DataItemID == b.DataItemID).AS<ec_dataitemEntity>(itemTableName)
                .Where((a, b) => symb2.Contains(b.DataItemCode))
                .OrderBy(a => a.OrderID)
                .Select((a, b) => new ec_dataitemdetailEntity()
                {
                    DataItemName = a.DataItemName,
                    DataItemNameEN = a.DataItemNameEN,
                    DataItemCode = a.DataItemCode,
                    ItemCode = b.DataItemCode,
                }).ToList();

            log4net.LogManager.GetLogger("INFO").Info(file.F_FilePath);

            switch (version)
            {
                case 1:
                    return ExportExcel1642(file.F_FilePath, details, items);
                case 2:
                    return ExportExcel8600(file.F_FilePath, details, items);
                case 3:
                    return ExportExcel8600SignalRow(file.F_FilePath, details, items);
            }
            return null;
        }
        /// <summary>
        /// 导出信号数据
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="details"></param>
        /// <param name="items"></param>
        /// <returns></returns>
        public byte[] ExportExcel1642(string filePath, List<ec_dataitemdetailEntity> groups, List<ec_dataitemdetailEntity> codelists)
        {
            byte[] bytes = null;
            try
            {
                ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
                if (!File.Exists(filePath))
                {
                    bytes = System.Text.Encoding.UTF8.GetBytes("服务器的硬盘上，找不到对应的模板文件：" + filePath);
                    return bytes;
                }
                using (var package = new ExcelPackage(filePath))
                {
                    var sheet = package.Workbook.Worksheets[0];
                    ExportSPEC(sheet, groups, codelists);
                    sheet = package.Workbook.Worksheets[1];
                    int rowNumber = 5; //excel里的第五行开始
                    groups.ForEach(x =>
                    {
                        sheet.Row(rowNumber).Height = 21.6; // 20;
                        sheet.Cells[$"C{rowNumber}:D{rowNumber}"].Style.Font.Bold = true; // 加粗
                        sheet.Cells[$"C{rowNumber}"].Value = x.DataItemCode; //组的编号
                        sheet.Cells[$"D{rowNumber}"].Value = x.DataItemName; //组名
                        rowNumber++;
                        x.Signals.Where(y => y.Signal_Group == x.DataItemCode).ToList().ForEach(y =>
                        {
                            sheet.Row(rowNumber).Height = 21.6; // 20;
                            if (y.DeleteFlg || y.Status == WireGroupStatusEnum.ToDelete)
                            {
                                sheet.Cells[$"C{rowNumber}:Y{rowNumber}"].Style.Font.Color.SetColor(System.Drawing.Color.Red);
                                sheet.Cells[$"C{rowNumber}:Y{rowNumber}"].Style.Font.Strike = true; //删除线的标记
                            }
                            sheet.Cells[$"C{rowNumber}"].Value = y.Group_Name;

                            if (y.Group_Desc_EN.Length > 30)
                            {
                                sheet.Cells[$"D{rowNumber}"].Style.Font.Color.SetColor(System.Drawing.Color.Red); //超出某个长度后
                            }
                            sheet.Cells[$"D{rowNumber}"].Value = y.Group_Desc_EN; //英文描述
                            sheet.Cells[$"H{rowNumber}"].Value = y.Code;
                            if (y.IO_Type != "N/A")
                            {
                                sheet.Cells[$"I{rowNumber}"].Value = y.IO_Type;  //信号类型
                            }
                            sheet.Cells[$"J{rowNumber}"].Value = y.Range_Min;
                            sheet.Cells[$"K{rowNumber}"].Value = y.Range_Max;
                            sheet.Cells[$"L{rowNumber}"].Value = y.Unit;
                            sheet.Cells[$"M{rowNumber}"].Value = y.Alarm_LL;
                            sheet.Cells[$"N{rowNumber}"].Value = y.Alarm_L;
                            sheet.Cells[$"O{rowNumber}"].Value = y.Alarm_H;
                            sheet.Cells[$"P{rowNumber}"].Value = y.Alarm_HH;
                            sheet.Cells[$"S{rowNumber}"].Value = y.AL_GRP.Replace("|", ",");
                            sheet.Cells[$"T{rowNumber}"].Value = y.BL_GRP.Replace("|", ",");
                            sheet.Cells[$"U{rowNumber}"].Value = y.Time_Delay;
                            sheet.Cells[$"V{rowNumber}"].Value = y.Supplier;
                            sheet.Cells[$"W{rowNumber}"].Value = y.SENSOR_CODE; //还需要合并单元格
                            if (y.NewSoftTags.Count > 0)
                            {
                                sheet.Cells[$"W{rowNumber}:W{rowNumber + y.NewSoftTags.Count}"].Merge = false;
                                sheet.Cells[$"W{rowNumber}:W{rowNumber + y.NewSoftTags.Count}"].Merge = true;
                            }

                            if (y.VDR_Record)
                            {
                                sheet.Cells[$"X{rowNumber}"].Value = "√";
                            }
                            if (y.SLD && y.SHD)
                            {
                                sheet.Cells[$"Y{rowNumber}"].Value = "SHD&SLD";
                            }
                            else if (y.SLD)
                            {
                                sheet.Cells[$"Y{rowNumber}"].Value = "SLD";
                            }
                            else if (y.SHD)
                            {
                                sheet.Cells[$"Y{rowNumber}"].Value = "SHD";
                            }
                            if (y.CommunicationPoint && !y.Remarks.ToUpper().Contains("MODBUS") && !y.Remarks.ToUpper().Contains("RS485") && !y.Remarks.ToUpper().Contains("RS422"))
                            {
                                sheet.Cells[$"Z{rowNumber}"].Value = y.Remarks + " MODBUS";
                            }
                            else
                            {
                                sheet.Cells[$"Z{rowNumber}"].Value = y.Remarks;
                            }
                            rowNumber++;
                            #region softtag
                            y.NewSoftTags.ForEach(z =>
                            {
                                sheet.Row(rowNumber).Height = 21.6;//20;
                                if (z.Group_Desc_EN.Length > 30)
                                {
                                    sheet.Cells[$"D{rowNumber}"].Style.Font.Color.SetColor(System.Drawing.Color.Red); //超出某个长度后
                                }
                                sheet.Cells[$"D{rowNumber}"].Value = z.Group_Desc_EN;
                                sheet.Cells[$"H{rowNumber}"].Value = z.Code;
                                sheet.Cells[$"M{rowNumber}"].Value = z.Alarm_LL;
                                sheet.Cells[$"N{rowNumber}"].Value = z.Alarm_L;
                                sheet.Cells[$"O{rowNumber}"].Value = z.Alarm_H;
                                sheet.Cells[$"P{rowNumber}"].Value = z.Alarm_HH;
                                sheet.Cells[$"S{rowNumber}"].Value = z.AL_GRP.Replace("|", ","); //虚拟点
                                sheet.Cells[$"T{rowNumber}"].Value = z.BL_GRP.Replace("|", ","); //虚拟点
                                sheet.Cells[$"U{rowNumber}"].Value = z.Time_Delay;
                                if (z.VDR_Record)
                                {
                                    sheet.Cells[$"X{rowNumber}"].Value = "√";
                                }
                                if (z.SHD && !z.Group_Desc_EN.EndsWith("(SHD)"))
                                {
                                    sheet.Cells[$"D{rowNumber}"].Value += "(SHD)";
                                }
                                else if (z.SLD && !z.Group_Desc_EN.EndsWith("(SLD)"))
                                {
                                    sheet.Cells[$"D{rowNumber}"].Value += "(SLD)";
                                }

                                if (sheet.Cells[$"D{rowNumber}"].Value.ToString().Length > 30)
                                {
                                    sheet.Cells[$"D{rowNumber}"].Style.Font.Color.SetColor(System.Drawing.Color.Red); //超出某个长度后
                                }
                                if (z.SLD && z.SHD)
                                {
                                    sheet.Cells[$"Y{rowNumber}"].Value = "SHD&SLD";
                                }
                                else if (z.SLD)
                                {
                                    sheet.Cells[$"Y{rowNumber}"].Value = "SLD";
                                }
                                else if (z.SHD)
                                {
                                    sheet.Cells[$"Y{rowNumber}"].Value = "SHD";
                                }
                                if (z.CommunicationPoint && !z.Remarks.ToUpper().Contains("MODBUS") && !z.Remarks.ToUpper().Contains("RS485") && !z.Remarks.ToUpper().Contains("RS422"))
                                {
                                    sheet.Cells[$"Z{rowNumber}"].Value = z.Remarks + " MODBUS";
                                }
                                else
                                {
                                    sheet.Cells[$"Z{rowNumber}"].Value = z.Remarks;
                                }
                                rowNumber++;
                            });
                            #endregion


                        });
                    });


                    var setting = package.Workbook.Worksheets["Setting"];
                    if (setting != null)
                    {
                        Setting(sheet, setting);
                        package.Workbook.Worksheets.Delete("Setting");
                    }


                    #region 设置打印区域、分页符等
                    sheet.PrinterSettings.PrintArea = sheet.Cells[$"A1:Z{rowNumber - 1}"];//打印区域
                                                                                          //sh.PrinterSettings.TopMargin = (Decimal)(topMargin / 2.54); //1.4是用户设置的cm，2.54是in和cm的比例，25.4就是in和mm的比例
                                                                                          //sh.PrinterSettings.BottomMargin = (Decimal)(bottomMargin / 2.54);
                                                                                          //sh.PrinterSettings.BottomMargin = 0.4M;
                                                                                          //sh.PrinterSettings.LeftMargin = 0.8M;
                                                                                          //sh.PrinterSettings.RightMargin = 0.8M;
                    sheet.PrinterSettings.RepeatRows = new ExcelAddress($"1:{(5 - 1)}");
                    #endregion 设置打印区域、分页符等
                    bytes = package.GetAsByteArray();
                }
            }
            catch (Exception ex)
            {
                log4net.LogManager.GetLogger("ERROR").Error("ec_Wire_GroupBLL，ExportExcel1462 1462信号点表导出" + ex.Message + ex.StackTrace);
                bytes = System.Text.Encoding.UTF8.GetBytes(ex.Message + ex.StackTrace);
            }
            return bytes;
        }
        /// <summary>
        /// 处理2行的数据。把数据拆分，第一段第一行；其他第二行，且|替换为，
        /// </summary>
        /// <param name="value"></param>
        /// <param name="sheet"></param>
        private void ExportExcel8600Helper(string value, string col, int rowNumber, ref ExcelWorksheet sheet)
        {
            char separator = '|';
            if (!string.IsNullOrEmpty(value))
            {
                var res = value.Split(separator);
                switch (res.Length)
                {
                    case 1:
                        sheet.Cells[$"{col}{rowNumber}"].Value = res[0];
                        break;
                    default:
                        sheet.Cells[$"{col}{rowNumber}"].Value = res[0];
                        sheet.Cells[$"{col}{rowNumber + 1}"].Value = value.Substring(value.IndexOf(separator) + 1).Replace(separator, ',');
                        break;
                }
            }
        }
        /// <summary>
        /// 导出信号数据
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="details"></param>
        /// <returns></returns>
        public byte[] ExportExcel8600(string filePath, List<ec_dataitemdetailEntity> groups, List<ec_dataitemdetailEntity> codelists)
        {
            string templateSheetName = "MES-9";
            byte[] bytes = null; //也可以用作是错误信息
            try
            {
                ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
                if (!File.Exists(filePath))
                {
                    bytes = System.Text.Encoding.UTF8.GetBytes("服务器的硬盘上，找不到对应的模板文件：" + filePath);
                    return bytes;
                }
                using (var package = new ExcelPackage(filePath))
                {
                    ExportSYMB2(package.Workbook.Worksheets["SYMB-2"], codelists);

                    if (!package.Workbook.Worksheets.Any(x => x.Name == templateSheetName))
                    {
                        bytes = System.Text.Encoding.UTF8.GetBytes("excel模板中，找不到这个sheet：" + templateSheetName);
                        return bytes;
                    }
                    groups.ForEach(x =>
                    {
                        package.Workbook.Worksheets[templateSheetName].Cells.Style.ShrinkToFit = true;//单元格字体缩放
                        var sheet = package.Workbook.Worksheets.Copy(templateSheetName, x.DataItemCode); //MES-9  这个sheet 当作是sheet的模板
                        int startRow = 8;
                        int rowNumber = startRow;
                        var list = x.Signals.Where(y => y.Signal_Group == x.DataItemCode).ToList();
                        list.ForEach(y =>
                        {
                            sheet.Cells["A6:X7"].Copy(sheet.Cells[$"A{rowNumber}:X{rowNumber + 1}"]); //复制一下
                            if (y.DeleteFlg || y.Status == WireGroupStatusEnum.ToDelete)
                            {
                                sheet.Cells[$"A{rowNumber}:X{rowNumber}"].Style.Font.Color.SetColor(System.Drawing.Color.Red);
                                sheet.Cells[$"A{rowNumber}:X{rowNumber}"].Style.Font.Strike = true;
                            }
                            sheet.Cells[$"A{rowNumber}"].Value = y.Group_Name;
                            sheet.Cells[$"B{rowNumber}"].Value = y.Group_Desc_EN;
                            if (y.Group_Desc_EN.Length > 30)
                            {
                                sheet.Cells[$"B{rowNumber}"].Style.Font.Color.SetColor(System.Drawing.Color.Red);
                            }
                            sheet.Cells[$"B{rowNumber + 1}"].Value = y.Group_Desc;
                            if (y.Group_Desc.Length > 30)
                            {
                                sheet.Cells[$"B{rowNumber + 1}"].Style.Font.Color.SetColor(System.Drawing.Color.Red);
                            }
                            sheet.Cells[$"D{rowNumber}"].Value = y.InOrOut;
                            #region 模拟量或者开关量
                            switch (y.IO_Type.ToUpper().Trim())
                            {
                                case "":
                                    break;
                                case "N/A":
                                    break;
                                case "NO":
                                case "NC":
                                    sheet.Cells[$"F{rowNumber}"].Value = y.IO_Type;
                                    if (string.IsNullOrEmpty(y.InOrOut))
                                    {
                                        sheet.Cells[$"D{rowNumber}"].Value = "DI";
                                    }
                                    break;
                                default:
                                    if (string.IsNullOrEmpty(y.InOrOut))
                                    {
                                        sheet.Cells[$"D{rowNumber}"].Value = "AI";
                                    }
                                    sheet.Cells[$"E{rowNumber}"].Value = y.IO_Type;
                                    break;
                            }
                            #endregion

                            sheet.Cells[$"G{rowNumber}"].Value = !string.IsNullOrEmpty(y.Range_Min) ? $"{y.Range_Min}~{y.Range_Max}" : y.Range_Max;
                            sheet.Cells[$"H{rowNumber}"].Value = y.Unit;

                            sheet.Cells[$"K{rowNumber}"].Value = y.SENSOR_CODE;
                            if (y.CommunicationPoint && !y.SENSOR_CODE.ToUpper().Contains("MODBUS"))
                            {
                                sheet.Cells[$"K{rowNumber + 1}"].Value = " MODBUS";
                            }
                            sheet.Cells[$"L{rowNumber}"].Value = y.Supplier;
                            sheet.Cells[$"S{rowNumber}"].Value = y.Time_Delay;
                            sheet.Cells[$"T{rowNumber}"].Value = y.BL_GRP.Replace("|", ","); ;
                            sheet.Cells[$"U{rowNumber}"].Value = y.VDR_Record ? "√" : ""; //23 12 26新增
                            sheet.Cells[$"V{rowNumber}"].Value = y.AL_GRP.Replace("|", ","); ; //u->v
                            sheet.Cells[$"Y{rowNumber}"].Value = y.Remarks;//x->y

                            //if (y.CommunicationPoint && !y.Remarks.ToUpper().Contains("MODBUS") 
                            //&& !y.Remarks.ToUpper().Contains("RS485") 
                            //&& !y.Remarks.ToUpper().Contains("RS422")
                            //&& !y.SENSOR_CODE.ToUpper().Contains("MODBUS"))
                            //{
                            //    sheet.Cells[$"K{rowNumber + 1}"].Value = " MODBUS";
                            //}

                            #region eccwccbcc 
                            ExportExcel8600Helper(y.WHConsole, "M", rowNumber, ref sheet);
                            ExportExcel8600Helper(y.WHCPU, "N", rowNumber, ref sheet);
                            ExportExcel8600Helper(y.ECRConsole, "O", rowNumber, ref sheet);
                            ExportExcel8600Helper(y.ECRCPU, "P", rowNumber, ref sheet);
                            ExportExcel8600Helper(y.ShipOfficeConsole, "Q", rowNumber, ref sheet);
                            ExportExcel8600Helper(y.ShipOfficeCPU, "R", rowNumber, ref sheet);
                            #endregion
                            #region 安全保护
                            if (y.SLD)
                            {
                                sheet.Cells[$"W{rowNumber}"].Value += "SLD";//v->w
                            }
                            else if (y.SHD)
                            {
                                sheet.Cells[$"W{rowNumber}"].Value += "SHD";
                            }
                            sheet.Cells[$"V{rowNumber + 1}"].Value += y.SafetyDelay;
                            //sheet.Cells[$"V{rowNumber}"].Value = y.SafetyProt;
                            #endregion

                            sheet.Cells[$"X{rowNumber}"].Value = y.AutoCtrl;//w->x

                            #region 报警值 连锁值
                            var Prow1 = sheet.Cells["P" + rowNumber].Text;
                            var Prow2 = sheet.Cells["P" + (rowNumber + 1)].Text;
                            //不取I结尾判断
                            if (Prow1.EndsWith("I"))
                            {
                                Prow1 = Prow2;
                                Prow2 = "";
                            }
                            //和导入的逻辑一致
                            if (Regex.Match(Prow1, ".AH/.AL").Success
                            || Prow1.EndsWith("H/L")
                            || (Prow1.EndsWith("AH") && Prow2.EndsWith("AL")))
                            {
                                sheet.Cells[$"I{rowNumber}"].Value = y.Alarm_H;
                                sheet.Cells[$"I{rowNumber + 1}"].Value = y.Alarm_L;
                                sheet.Cells[$"J{rowNumber}"].Value = y.Alarm_HH;
                                sheet.Cells[$"J{rowNumber + 1}"].Value = y.Alarm_LL;
                            }
                            else if (Regex.Match(Prow1, ".AL/.AH").Success
                            || Prow1.EndsWith("L/H")
                            || Prow1.EndsWith("AL") && Prow2.EndsWith("AH"))
                            {
                                sheet.Cells[$"I{rowNumber}"].Value = y.Alarm_L;
                                sheet.Cells[$"I{rowNumber + 1}"].Value = y.Alarm_H;
                                sheet.Cells[$"J{rowNumber}"].Value = y.Alarm_LL;
                                sheet.Cells[$"J{rowNumber + 1}"].Value = y.Alarm_HH;
                            }
                            else if (Prow1.EndsWith("AH") || Prow1.EndsWith("AHH"))
                            {

                                sheet.Cells[$"I{rowNumber}"].Value = y.Alarm_H;
                                sheet.Cells[$"J{rowNumber}"].Value = y.Alarm_HH;


                            }
                            else if (Prow1.EndsWith("AL"))
                            {
                                sheet.Cells[$"I{rowNumber}"].Value = y.Alarm_L;
                                sheet.Cells[$"J{rowNumber}"].Value = y.Alarm_LL;

                            }
                            else if (Prow1.EndsWith("A"))
                            {
                                sheet.Cells[$"I{rowNumber}"].Value = y.Alarm_H;
                                sheet.Cells[$"J{rowNumber}"].Value = y.Alarm_HH;

                            }
                            else
                            {
                                sheet.Cells[$"I{rowNumber}"].Value = y.Alarm_L;
                                sheet.Cells[$"I{rowNumber + 1}"].Value = y.Alarm_H;
                                sheet.Cells[$"J{rowNumber}"].Value = y.Alarm_LL;
                                sheet.Cells[$"J{rowNumber + 1}"].Value = y.Alarm_HH;
                            }


                            #endregion

                            rowNumber += 2;
                        });
                        //sheet.DeleteRow(startRow - 2, 2);  //删掉模板行
                        //000
                        sheet.Cells[$"A{(startRow - 2)}"].Value = x.DataItemCode.EndsWith("000") ? x.DataItemCode : x.DataItemCode + "000";
                        sheet.Cells[$"B{(startRow - 2)}"].Value = x.DataItemNameEN;
                        sheet.Cells[$"B{(startRow - 1)}"].Value = x.DataItemName;

                        //合并备注
                        string remark = "";
                        int j = 6;
                        for (int i = 6; i < sheet.Dimension.End.Row; i = i + 2)
                        {
                            var curmark = sheet.Cells[$"Y{i}"].Text;//备注列在Y。X是自动控制
                            if (remark != curmark)
                            {
                                if (!string.IsNullOrEmpty(remark) && j + 2 < i)
                                {
                                    sheet.Cells[$"Y{j}:Y{i - 1}"].Merge = true;
                                }
                                remark = curmark;
                                j = i;
                            }
                        }
                        sheet.PrinterSettings.PrintArea = sheet.Cells[$"A1:Y{list.Count * 2 + 7}"]; //每个信号占据2行，然后5代表表头
                    });
                    package.Workbook.Worksheets.Delete(templateSheetName); //删掉模板sheet
                    var cont = package.Workbook.Worksheets["CONT-1"];
                    if (cont == null)
                    {
                        bytes = System.Text.Encoding.UTF8.GetBytes("excel模板中，找不到这个目录页sheet：" + "CONT-1");
                        return bytes;
                    }
                    for (int i = 0; i < groups.Count / 2; i++)
                    {
                        cont.InsertRow(13 + i * 3, 3);  //在13行，每次插入3行
                        cont.Cells["A10:J12"].Copy(cont.Cells[$"A{13 + i * 3}:X{15 + i * 3}"]);
                    }
                    uint pageNumber = 1;
                    for (int i = 0; i < package.Workbook.Worksheets.Count; i++)
                    {
                        var sheet = package.Workbook.Worksheets[i];
                        if (sheet.Name == "Setting") continue;
                        if (i == 0) //"CONT-1" 页
                        {
                            double h = 0;
                            for (int j = 0; j < groups.Count / 2 * 3 + 16; j++)
                            {
                                h += cont.Row(j + 1).Height;
                            }
                            uint p = (uint)h / 600 + 1;
                            cont.Cells[$"E7"].Value = $"  {pageNumber} ~ {pageNumber + p - 1}";
                            pageNumber += p;
                        }
                        else if (i == 1) // symbol 页
                        {
                            cont.Cells[$"J7"].Value = $"  {pageNumber} ~ {pageNumber + 1}";
                            pageNumber += 2;
                        }
                        else   // 具体的每一个group 页
                        {
                            double h = 0;
                            var detail = groups.First(x => x.DataItemCode == sheet.Name);
                            for (int j = 0; j < detail.Signals.Count * 2 + 5; j++) //每页的行数
                            {
                                h += sheet.Row(j + 1).Height;
                            }
                            uint p = ((uint)h - 200) / 500 + 1;

                            if (i - 2 > groups.Count / 2)
                            {
                                //偶数，右侧
                                cont.Cells[$"G{4 + (i - groups.Count / 2 - 1) * 3}"].Value = sheet.Name + "000"; //往 目录页 写入组名 和 页码
                                cont.Cells[$"H{4 + (i - groups.Count / 2 - 1) * 3}"].Value = detail.DataItemNameEN;
                                cont.Cells[$"H{5 + (i - groups.Count / 2 - 1) * 3}"].Value = detail.DataItemName;
                                cont.Cells[$"J{4 + (i - groups.Count / 2 - 1) * 3}"].Value = $"  {pageNumber} ~ {pageNumber + p - 1}";
                            }
                            else
                            {
                                //左侧
                                cont.Cells[$"B{4 + i * 3}"].Value = sheet.Name + "000";
                                cont.Cells[$"C{4 + i * 3}"].Value = detail.DataItemNameEN;
                                cont.Cells[$"C{5 + i * 3}"].Value = detail.DataItemName; //第二行
                                cont.Cells[$"E{4 + i * 3}"].Value = $"  {pageNumber} ~ {pageNumber + p - 1}";
                            }
                            pageNumber += p;
                        }
                    }

                    var setting = package.Workbook.Worksheets["Setting"];
                    groups.ForEach(x =>
                    {
                        var sheet = package.Workbook.Worksheets[x.DataItemCode];
                        ExportSheetName(sheet, x);
                        Setting(sheet, setting);
                    });
                    if (setting != null)
                    {
                        package.Workbook.Worksheets.Delete("Setting");
                    }

                    bytes = package.GetAsByteArray();
                }

            }
            catch (Exception ex)
            {
                log4net.LogManager.GetLogger("ERROR").Error("ec_Wire_GroupBLL，ExportExcel8600 8600信号点表导出" + ex.Message + ex.StackTrace);
                bytes = System.Text.Encoding.UTF8.GetBytes(ex.Message + ex.StackTrace);
            }
            return bytes;
        }

        /// <summary>
        /// 导出信号数据
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="details"></param>
        /// <returns></returns>
        public byte[] ExportExcel8600SignalRow(string filePath, List<ec_dataitemdetailEntity> groups, List<ec_dataitemdetailEntity> codelists)
        {

            string templateSheetName = "MES-9";
            byte[] bytes = null; //也可以用作是错误信息
            try
            {
                ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
                if (!File.Exists(filePath))
                {
                    bytes = System.Text.Encoding.UTF8.GetBytes("服务器的硬盘上，找不到对应的模板文件：" + filePath);
                    return bytes;
                }
                using (var package = new ExcelPackage(filePath))
                {
                    ExportSYMB2(package.Workbook.Worksheets["SYMB-2"], codelists);

                    if (!package.Workbook.Worksheets.Any(x => x.Name == templateSheetName))
                    {
                        bytes = System.Text.Encoding.UTF8.GetBytes("excel模板中，找不到这个sheet：" + templateSheetName);
                        return bytes;
                    }
                    groups.ForEach(x =>
                    {
                        package.Workbook.Worksheets[templateSheetName].Cells.Style.ShrinkToFit = true;//单元格字体缩放
                        var sheet = package.Workbook.Worksheets.Copy(templateSheetName, x.DataItemCode); //MES-9  这个sheet 当作是sheet的模板
                        int startRow = 7;
                        int rowNumber = startRow;
                        var list = x.Signals.Where(y => y.Signal_Group == x.DataItemCode).ToList();
                        list.ForEach(y =>
                        {
                            sheet.Cells["A6:X6"].Copy(sheet.Cells[$"A{rowNumber}:X{rowNumber}"]); //复制一下
                            if (y.DeleteFlg || y.Status == WireGroupStatusEnum.ToDelete)
                            {
                                sheet.Cells[$"A{rowNumber}:X{rowNumber}"].Style.Font.Color.SetColor(System.Drawing.Color.Red);
                                sheet.Cells[$"A{rowNumber}:X{rowNumber}"].Style.Font.Strike = true;
                            }
                            sheet.Cells[$"A{rowNumber}"].Value = y.Group_Name;
                            sheet.Cells[$"B{rowNumber}"].Value = y.Group_Desc_EN;
                            //if (y.Group_Desc_EN.Length > 30)
                            //{
                            //    sheet.Cells[$"B{rowNumber}"].Style.Font.Color.SetColor(System.Drawing.Color.Red);
                            //}
                            //if (y.Group_Desc.Length > 30)
                            //{
                            //    sheet.Cells[$"B{rowNumber + 1}"].Style.Font.Color.SetColor(System.Drawing.Color.Red);
                            //}
                            sheet.Cells[$"D{rowNumber}"].Value = y.InOrOut;
                            #region 模拟量或者开关量
                            switch (y.IO_Type.ToUpper().Trim())
                            {
                                case "":
                                    break;
                                case "NO":
                                case "NC":
                                    sheet.Cells[$"F{rowNumber}"].Value = y.IO_Type;
                                    if (string.IsNullOrEmpty(y.InOrOut))
                                    {
                                        sheet.Cells[$"D{rowNumber}"].Value = "DI";
                                    }
                                    break;
                                default:
                                    if (string.IsNullOrEmpty(y.InOrOut))
                                    {
                                        sheet.Cells[$"D{rowNumber}"].Value = "AI";
                                    }
                                    sheet.Cells[$"E{rowNumber}"].Value = y.IO_Type;
                                    break;
                            }
                            #endregion

                            sheet.Cells[$"G{rowNumber}"].Value = !string.IsNullOrEmpty(y.Range_Min) ? $"{y.Range_Min}~{y.Range_Max}" : y.Range_Max;
                            sheet.Cells[$"H{rowNumber}"].Value = y.Unit;

                            #region 报警值 连锁值 
                            sheet.Cells[$"I{rowNumber}"].Value = y.Alarm_H + "\n\r" + y.Alarm_L;
                            sheet.Cells[$"J{rowNumber}"].Value = y.Alarm_HH + "\n\r" + y.Alarm_LL;
                            #endregion

                            sheet.Cells[$"K{rowNumber}"].Value = y.SENSOR_CODE;
                            if (y.CommunicationPoint)
                            {
                                sheet.Cells[$"L{rowNumber}"].Value = "MODBUS";
                            }
                            sheet.Cells[$"M{rowNumber}"].Value = y.Supplier;

                            #region eccwccbcc
                            sheet.Cells[$"N{rowNumber}"].Value = y.WHConsole.Replace("|", ",");
                            sheet.Cells[$"O{rowNumber}"].Value = y.WHCPU.Replace("|", ",");
                            sheet.Cells[$"P{rowNumber}"].Value = y.ECRConsole.Replace("|", ",");
                            sheet.Cells[$"Q{rowNumber}"].Value = y.ECRCPU.Replace("|", ",");
                            sheet.Cells[$"R{rowNumber}"].Value = y.ShipOfficeConsole.Replace("|", ",");
                            sheet.Cells[$"S{rowNumber}"].Value = y.ShipOfficeCPU.Replace("|", ",");
                            #endregion

                            sheet.Cells[$"T{rowNumber}"].Value = y.Time_Delay;
                            sheet.Cells[$"U{rowNumber}"].Value = y.BL_GRP.Replace("|", ",");
                            sheet.Cells[$"V{rowNumber}"].Value = y.VDR_Record ? "√" : ""; //23 12 26新增
                            sheet.Cells[$"W{rowNumber}"].Value = y.AL_GRP.Replace("|", ","); //u->v



                            #region 安全保护
                            if (y.SLD)
                            {
                                sheet.Cells[$"X{rowNumber}"].Value += "SLD";//v->w
                            }
                            else if (y.SHD)
                            {
                                sheet.Cells[$"X{rowNumber}"].Value += "SHD";
                            }
                            //sheet.Cells[$"V{rowNumber}"].Value = y.SafetyProt;
                            #endregion

                            sheet.Cells[$"Y{rowNumber}"].Value = y.AutoCtrl;//w->x
                            sheet.Cells[$"Z{rowNumber}"].Value = y.Remarks;//x->y


                            rowNumber += 1;
                        });
                        //sheet.DeleteRow(startRow - 2, 2);  //删掉模板行
                        //000
                        sheet.Cells[$"A{(startRow - 1)}"].Value = x.DataItemCode.EndsWith("000") ? x.DataItemCode : x.DataItemCode + "000";
                        sheet.Cells[$"B{(startRow - 1)}"].Value = x.DataItemNameEN;

                        sheet.PrinterSettings.PrintArea = sheet.Cells[$"A1:Z{list.Count + 7}"]; //每个信号占据2行，然后5代表表头
                    });
                    package.Workbook.Worksheets.Delete(templateSheetName); //删掉模板sheet
                    var cont = package.Workbook.Worksheets["CONT-1"];
                    if (cont == null)
                    {
                        bytes = System.Text.Encoding.UTF8.GetBytes("excel模板中，找不到这个目录页sheet：" + "CONT-1");
                        return bytes;
                    }
                    for (int i = 0; i < groups.Count / 2; i++)
                    {
                        cont.InsertRow(13 + i * 3, 3);  //在13行，每次插入3行
                        cont.Cells["A10:J12"].Copy(cont.Cells[$"A{13 + i * 3}:X{15 + i * 3}"]);
                    }
                    uint pageNumber = 1;
                    for (int i = 0; i < package.Workbook.Worksheets.Count; i++)
                    {
                        var sheet = package.Workbook.Worksheets[i];
                        if (sheet.Name == "Setting") continue;
                        if (i == 0) //"CONT-1" 页
                        {
                            double h = 0;
                            for (int j = 0; j < groups.Count / 2 * 3 + 16; j++)
                            {
                                h += cont.Row(j + 1).Height;
                            }
                            uint p = (uint)h / 600 + 1;
                            cont.Cells[$"E7"].Value = $"  {pageNumber} ~ {pageNumber + p - 1}";
                            pageNumber += p;
                        }
                        else if (i == 1) // symbol 页
                        {
                            cont.Cells[$"J7"].Value = $"  {pageNumber} ~ {pageNumber + 1}";
                            pageNumber += 2;
                        }
                        else   // 具体的每一个group 页
                        {
                            double h = 0;
                            var detail = groups.First(x => x.DataItemCode == sheet.Name);
                            for (int j = 0; j < detail.Signals.Count * 2 + 5; j++) //每页的行数
                            {
                                h += sheet.Row(j + 1).Height;
                            }
                            uint p = ((uint)h - 200) / 500 + 1;

                            if (i - 2 > groups.Count / 2)
                            {
                                //偶数，右侧
                                cont.Cells[$"G{4 + (i - groups.Count / 2 - 1) * 3}"].Value = sheet.Name + "000"; //往 目录页 写入组名 和 页码
                                cont.Cells[$"H{4 + (i - groups.Count / 2 - 1) * 3}"].Value = detail.DataItemNameEN;
                                cont.Cells[$"H{5 + (i - groups.Count / 2 - 1) * 3}"].Value = detail.DataItemName;
                                cont.Cells[$"J{4 + (i - groups.Count / 2 - 1) * 3}"].Value = $"  {pageNumber} ~ {pageNumber + p - 1}";
                            }
                            else
                            {
                                //左侧
                                cont.Cells[$"B{4 + i * 3}"].Value = sheet.Name + "000";
                                cont.Cells[$"C{4 + i * 3}"].Value = detail.DataItemNameEN;
                                cont.Cells[$"C{5 + i * 3}"].Value = detail.DataItemName; //第二行
                                cont.Cells[$"E{4 + i * 3}"].Value = $"  {pageNumber} ~ {pageNumber + p - 1}";
                            }
                            pageNumber += p;
                        }
                    }
                    var setting = package.Workbook.Worksheets["Setting"];
                    groups.ForEach(x =>
                    {
                        var sheet = package.Workbook.Worksheets[x.DataItemCode];
                        ExportSheetName(sheet, x);
                        Setting(sheet, setting);
                    });
                    if (setting != null)
                    {
                        package.Workbook.Worksheets.Delete("Setting");
                    }
                    bytes = package.GetAsByteArray();
                }

            }
            catch (Exception ex)
            {
                log4net.LogManager.GetLogger("ERROR").Error("ec_Wire_GroupBLL，ExportExcel8600 8600信号点表导出" + ex.Message + ex.StackTrace);
                bytes = System.Text.Encoding.UTF8.GetBytes(ex.Message + ex.StackTrace);
            }
            return bytes;
        }
        /// <summary>
        /// 导出时SYMB-2工作簿
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="items"></param>
        void ExportSYMB2(ExcelWorksheet sheet, List<ec_dataitemdetailEntity> items)
        {
            if (sheet == null) return;
            var extenAlarmGroups = items.Where(x => x.ItemCode == "Exten_Alarm_Group").ToList();
            var sensorType = items.Where(x => x.ItemCode == "Sensor_Type").ToList();
            var alarm = items.Where(x => x.ItemCode == "Alarm").ToList();
            var control = items.Where(x => x.ItemCode == "Control").ToList();
            var indicator = items.Where(x => x.ItemCode == "Indicator").ToList();
            var others = items.Where(x => x.ItemCode == "Others").ToList();
            var maker = items.Where(x => x.ItemCode == "Maker").ToList();
            var part1 = new List<int>() { extenAlarmGroups.Count, sensorType.Count, alarm.Count / 2 }.Max();
            if (part1 > 0) part1 -= 1;//模板中有一块，0 1不需要新增 
            for (int i = 0; i < part1; i++)
            {
                sheet.InsertRow(11 + i * 3, 3);
                sheet.Cells["A8:I10"].Copy(sheet.Cells[$"A{11 + i * 3}:I{13 + i * 3}"]);
            }
            var part2 = new List<int>() { control.Count, indicator.Count, others.Count, maker.Count }.Max();
            for (int i = 0; i < part2 - 11; i++)
            {
                sheet.InsertRow(51 + part1 * 3 + i * 3, 3);
                sheet.Cells["A8:I10"].Copy(sheet.Cells[$"A{51 + part1 * 3 + i * 3}:I{53 + part1 * 3 + i * 3}"]);
            }
            for (int i = 0; i < extenAlarmGroups.Count; i++)
            {
                sheet.Cells[$"A{8 + i * 3}"].Value = extenAlarmGroups[i].DataItemCode;
                sheet.Cells[$"B{8 + i * 3}"].Value = extenAlarmGroups[i].DataItemNameEN;
                sheet.Cells[$"B{9 + i * 3}"].Value = extenAlarmGroups[i].DataItemName;
            }
            for (int i = 0; i < sensorType.Count; i++)
            {
                sheet.Cells[$"C{8 + i * 3}"].Value = sensorType[i].DataItemCode;
                sheet.Cells[$"D{8 + i * 3}"].Value = sensorType[i].DataItemNameEN;
                sheet.Cells[$"D{9 + i * 3}"].Value = sensorType[i].DataItemName;
            }
            for (int i = 0; i < alarm.Count; i++)
            {
                if (i % 2 == 0)
                {
                    sheet.Cells[$"E{8 + i / 2 * 3}"].Value = alarm[i].DataItemCode;
                    sheet.Cells[$"F{8 + i / 2 * 3}"].Value = alarm[i].DataItemNameEN;
                    sheet.Cells[$"F{9 + i / 2 * 3}"].Value = alarm[i].DataItemName;
                }
                if (i % 2 == 1)
                {
                    sheet.Cells[$"G{8 + i / 2 * 3}"].Value = alarm[i].DataItemCode;
                    sheet.Cells[$"H{8 + i / 2 * 3}"].Value = alarm[i].DataItemNameEN;
                    sheet.Cells[$"H{9 + i / 2 * 3}"].Value = alarm[i].DataItemName;
                }
            }
            for (int i = 0; i < control.Count; i++)
            {
                sheet.Cells[$"A{18 + part1 * 3 + i * 3}"].Value = control[i].DataItemCode;
                sheet.Cells[$"B{18 + part1 * 3 + i * 3}"].Value = control[i].DataItemNameEN;
                sheet.Cells[$"B{19 + part1 * 3 + i * 3}"].Value = control[i].DataItemName;
            }
            for (int i = 0; i < indicator.Count; i++)
            {
                sheet.Cells[$"C{18 + part1 * 3 + i * 3}"].Value = indicator[i].DataItemCode;
                sheet.Cells[$"D{18 + part1 * 3 + i * 3}"].Value = indicator[i].DataItemNameEN;
                sheet.Cells[$"D{19 + part1 * 3 + i * 3}"].Value = indicator[i].DataItemName;
            }
            for (int i = 0; i < others.Count; i++)
            {
                sheet.Cells[$"E{18 + part1 * 3 + i * 3}"].Value = others[i].DataItemCode;
                sheet.Cells[$"F{18 + part1 * 3 + i * 3}"].Value = others[i].DataItemNameEN;
                sheet.Cells[$"F{19 + part1 * 3 + i * 3}"].Value = others[i].DataItemName;
            }
            for (int i = 0; i < maker.Count; i++)
            {
                sheet.Cells[$"G{18 + part1 * 3 + i * 3}"].Value = maker[i].DataItemCode;
                sheet.Cells[$"H{18 + part1 * 3 + i * 3}"].Value = maker[i].DataItemNameEN;
                sheet.Cells[$"H{19 + part1 * 3 + i * 3}"].Value = maker[i].DataItemName;
            }
        }
        /// <summary>
        /// 导出时SPEC工作簿
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="details"></param>
        /// <param name="items"></param>
        void ExportSPEC(ExcelWorksheet sheet, List<ec_dataitemdetailEntity> details, List<ec_dataitemdetailEntity> items)
        {
            if (sheet == null) return;
            int rowNumber = 53;
            for (int i = 1; i < details.Count; i++)
            {
                sheet.InsertRow(rowNumber + i, 1);
                sheet.Cells["A53:H53"].Copy(sheet.Cells[$"A{rowNumber + i}:H{rowNumber + i}"]);
            }
            for (int i = 0; i < details.Count; i++)
            {
                sheet.Cells[$"A{rowNumber + i}"].Value = $"{details[i].DataItemCode} : {details[i].DataItemNameEN}";
            }
            if (details.Count > 1)
            {
                rowNumber += details.Count - 1;
            }
            rowNumber += 7;
            var extenAlarmGroups = items.Where(x => x.ItemCode == "Exten_Alarm_Group").ToList();
            for (int i = 1; i < extenAlarmGroups.Count; i++)
            {
                sheet.InsertRow(rowNumber + i, 1);
                sheet.Cells[$"A{rowNumber}:H{rowNumber}"].Copy(sheet.Cells[$"A{rowNumber + i}:H{rowNumber + i}"]);
            }
            for (int i = 0; i < extenAlarmGroups.Count; i++)
            {
                sheet.Cells[$"A{rowNumber + i}"].Value = $"{extenAlarmGroups[i].DataItemCode} : {extenAlarmGroups[i].DataItemNameEN}";
            }
            if (extenAlarmGroups.Count > 1)
            {
                rowNumber += extenAlarmGroups.Count - 1;
            }
            rowNumber += 3;
            var extensionAlarmPanels = items.Where(x => x.ItemCode == "Extension_Alarm_Panel").ToList();
            for (int i = 1; i < extensionAlarmPanels.Count; i++)
            {
                sheet.InsertRow(rowNumber + i, 1);
                sheet.Cells[$"A{rowNumber}:H{rowNumber}"].Copy(sheet.Cells[$"A{rowNumber + i}:H{rowNumber + i}"]);
            }
            for (int i = 0; i < extensionAlarmPanels.Count; i++)
            {
                sheet.Cells[$"A{rowNumber + i}"].Value = extensionAlarmPanels[i].DataItemNameEN;
            }
            if (extensionAlarmPanels.Count > 1)
            {
                rowNumber += extensionAlarmPanels.Count - 1;
            }
            rowNumber += 10;
            var alarmBlockingGroups = items.Where(x => x.ItemCode == "Alarm_Blocking_Group").ToList();
            for (int i = 1; i < alarmBlockingGroups.Count; i++)
            {
                sheet.InsertRow(rowNumber + i, 1);
                sheet.Cells[$"A{rowNumber}:H{rowNumber}"].Copy(sheet.Cells[$"A{rowNumber + i}:H{rowNumber + i}"]);
            }
            for (int i = 0; i < alarmBlockingGroups.Count; i++)
            {
                sheet.Cells[$"A{rowNumber + i}"].Value = $"{alarmBlockingGroups[i].DataItemCode} : {alarmBlockingGroups[i].DataItemNameEN}";
            }
        }

        /// <summary>
        /// 导出时SPEC工作簿的名字
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="detail"></param>
        void ExportSheetName(ExcelWorksheet sheet, ec_dataitemdetailEntity detail)
        {
            if (sheet == null) return;
            if (!string.IsNullOrEmpty(detail.Remark))
            {
                sheet.Name = detail.Remark;
            }
        }
        /// <summary>
        /// 按配置调整列（整列搬迁）。导入时，在正式导入之前对源表进行调整。
        /// </summary>
        /// <param name="sheet">不存在就退出</param>
        /// <param name="mappingSheet">不存在就退出</param>
        void Setting(ExcelWorksheet sheet, ExcelWorksheet mappingSheet)
        {
            if (sheet == null || mappingSheet == null) return;

            int start = 5;//起始行
            int.TryParse(mappingSheet.Cells["B1"].Text, out start);
            int end = sheet.Dimension.End.Row;//最后一行
            int last = sheet.Dimension.End.Column;

            for (int i = 3; i <= mappingSheet.Dimension.End.Row; i++)
            {
                var source = mappingSheet.Cells[$"A{i}"].Text;
                if (mappingSheet.Cells[$"C{i}"].Text == "是")
                {
                    sheet.Cells[$"{source}{start}:{source}{end}"].Clear();//清理
                    continue;
                }
                var target = CommonHelper.ToName(CommonHelper.ToIndex(source) + last);
                var sourceColumn = sheet.Cells[$"{source}{start}:{source}{end}"];
                sourceColumn.Copy(sheet.Cells[$"{target}{start}:{target}{end}"], ExcelRangeCopyOptionFlags.ExcludeStyles);
                sourceColumn.Clear();//老的搬走
            }
            for (int i = 3; i <= mappingSheet.Dimension.End.Row; i++)
            {
                if (mappingSheet.Cells[$"C{i}"].Text == "是") continue;
                var source = CommonHelper.ToName(CommonHelper.ToIndex(mappingSheet.Cells[$"A{i}"].Text) + last);
                var target = mappingSheet.Cells[$"B{i}"].Text;
                var sourceColumn = sheet.Cells[$"{source}{start}:{source}{end}"];
                sourceColumn.Copy(sheet.Cells[$"{target}{start}:{target}{end}"], ExcelRangeCopyOptionFlags.ExcludeStyles);//新的搬过来
                sourceColumn.Clear();
            }
        }
    }
}
